Extracting the value of the 'next' element in an array

I have an array with over 300 elements. Is there a way to loop through this array, extract the value of the ‘next’ element, assign that value to a variable to use in several other formulas, then more to the ‘next’ element in the original array, until the procedure had looped through all the elements in the original array? I do not want to build a second array, which is what it seems like the looparray statement does, but the documentation on the looparray statement is not clear to me.

Use the looparray statement.

Or something like this:

let myarray="a,b,c,d,e,f,g"
let lvsize=arraysize(myarray,",")
let n=1
loop
     let lvnext=array(myarray,n+1,",")
     alertsheet "Do something with "+lvnext
     n=n+1
     lvsize=lvsize-1
     stoploopif lvsize=1
 endloop

If you want to pick up the first element of the array, set n=0 and change stoploopif to lvsize=0.

In the loop:

looparray ArrayName, Separator, Element[, Index]
    [contents of loop]
endloop

for the first iteration of the loop the variable Element contains the first element of the array, and the optional variable Index contains 1. For the second iteration Element contains the second element of the array and Index contains 2, and so on.

Thus Panorama doesn’t have to waste time parsing the array anew each time, finding numbered elements by counting from the start, as it would do if you were to use a for loop and find the array element by number in each iteration.

The reference to making a copy of the array is purely an internal matter. The looparray statement works on the array in the form it exists when the looparray statement is found. If the contents of the loop changes the variable in which the array is stored during the course of execution it will have no effect on the successive array elements which are made available in the variable Element.

In other words

let Array="A-B-C-D"
looparray Array, "-", Element, Index
    Array="Hello"+Index
    message Element
endloop

will give you four messages containing “A”, “B”, “C” and “D”, even though the four iterations of the loop change the contents of Array from “A-B-C-D” to “Hello1”, “Hello2”, “Hello3” and “Hello4”.

For the reasons described by @pcnewble, the looparray is definitely the best (and fastest) method to perform this task.

I appreciate the suggestions from those who have made them, but I’m still stuck. As a non-programmer, I’m still not understanding how the looparray statement functions. I have two dbs: “Contacts” and “Donations”, which are linked via the «ContactID» field. There are multiple entries under numerous of those unique numbers in “Donations”. I can build an array of the unique number of all the “Contacts” for whom there is a record in “Donations”. For each element in that array, I want to calculate 10 values from “Donations” (using various lookuplast( and aggregate( functions) and store them in “Contacts”. I have been told that I can do that using the looparray statement, but I have not been able to do so. For example, can someone help me to understand why the following procedure, which is for 2 of the 10 values I’d like to have, does not work? Thanks in advance!!!
//start in “Contacts” db
global gvdonor
local lvnumber
rememberwindow
opendatabase “Donations”
setactivedatabase “Donations”
field «ContactID»
groupup
outlinelevel 1
lastrecord
deleterecord
let gvdonor=arrayselectedbuild(", “, “”, “ContactID”,”")
selectall
removesummaries 7
field «Date»
sortup
lastrecord
setactivedatabase “Contacts”
originalwindow
field «lastdate»
looparray gvdonor, “, “, lvnumber
«lastdate»=lookuplast(“Donations”, «ContactID», lvnumber, «Date», “”) «totalsum»=aggregate(“Amount”, “sum”,”«ContactID»=lvnumber”, “Donations”, true())
endloop

I’m not sure what “does not work” means to you. You haven’t described any of the symptoms. The main problem I see here is that your loop doesn’t contain any code to add records or move to another record. It just keeps changing the values of lastdate and totalsum in the current record. I doubt if that is the result you were aiming for.

Dave,
You are correct in what you say. What I want to do is produce a value for for lastdate ad totalsum and store it in those fields in the ‘Contacts’ db for each of the elements in the array. How do I do that?

That depends on whether these values will be going into existing records, or added records. If it’s the latter, you need an AddRecord command, after the LoopArray statement. If it’s the former, you need to do a Find to move to the appropriate record.

With a better understanding of your requirements, it might be possible to recommend a different approach entirely.

Dave,

It is the latter, i.e., the values are going into exiting records corresponding to the element of the array. It is getting late here in the UK. I’ll pick this up with you tomorrow. Thanks for your help! I really appreciate it.

I have some generally notes about your code. First of all, there is no reason to make gvdonor a global variable, you are declaring it as a local later and that is fine. So I would remove this line - it’s best to avoid global variables if not needed.

global gvdonor

The line

local lvnumber

is also not needed, this variable is automatically created by looparray. But it doesn’t hurt to leave this line in if you want.

Secondly, the second line here should not be necessary, the opendatabase statement already makes this database active. The setactivedatabase would only be necessary if you used opensecret instead of opendatabase.

opendatabase “Donations”
setactivedatabase “Donations”

I think you are using these 8 lines of code to build an array of ContactID’s.

field «ContactID»
groupup
outlinelevel 1
lastrecord
deleterecord
let gvdonor=arrayselectedbuild(", “, “”, “ContactID”,”")
selectall
removesummaries 7

You should be able to replace all 8 lines with this much simpler code:

let gvdonor=listchoices(ContactID,", ")

I’m not sure what these three lines are for. They don’t accomplish anything for the code that follows.

field «Date»
sortup
lastrecord

The originalwindow statement will change the active database, so the setactivedatabase statement is superflous.

setactivedatabase “Contacts”
originalwindow

This statement doesn’t accomplish anything for the code that follows.

field «lastdate»

I’ve taken a shot at writing the loop code for you. Since I don’t know what field contains the id number, I had to guess at the field name. If it’s not DonorID, you’ll need to change that. Also I am assuming this field is a text field, if it is numeric you’ll have to convert lvnumber to a number using the val( function.

looparray gvdonor, “, “, lvnumber
    find DonorID=lvnumber
    if info("found")
        «lastdate»=lookuplast(“Donations”, «ContactID», lvnumber, «Date», “”)
        «totalsum»=aggregate(“Amount”, “sum”,”«ContactID»=lvnumber”, “Donations”, true())
    endif
endloop

The sortup puts the database in order by date, so that lookuplast will return the last date.

1 Like

First of all, thank you Dave and especially Jim. As you can see from my code, I have a lot to learn. I have changed the code to follow Jim’s suggestions. However, when I check the following procedure, I get a “Syntax Error’ message following the lvnumber in the «lastdate»= formula. I’ve tried to do various things, but keep getting the ‘Syntax Error’ message. What can I do to correct this?
local lvnumber
rememberwindow
opendatabase “Donations”
let gvdonor = listchoices(«ContactID»,”, ")
originalwindow

looparray gvdonor, “, “, lvnumber
lvnumber=val(lvnumber)
find DonorID=lvnumber
if info(“found”)
«lastdate»=lookuplast(“Donations”, «ContactID», lvnumber, «Date», “”)
«totalsum»=aggregate(“Amount”, “sum”,”«ContactID»=lvnumber”, “Donations”, true())
endif
endloop

I think it is a matter of auto-correction here in the forum that replaces the quotes. Try to replace the curly (typographic) quotes with straight quotes.

Kurt,
That did it! Thanks. I had copied the code from my word processor, which had curly quotes. Thanks again.

OK. Now, when I run the following procedure PanX crashes. Anyone know why that’s happening?

local lvnumber
rememberwindow
opendatabase “Donations”
let gvdonor = listchoices(«ContactID»,", ")
gvdonor=arrayrange(gvdonor, 5, 9, ", ")
originalwindow

looparray gvdonor, “, “, lvnumber
lvnumber=val(lvnumber)
find «ContactID»=lvnumber
if info(“found”)
«lastdate»=lookuplast(“Donations”, «ContactID», lvnumber, «Date», “”)
«totalsum»=aggregate(“Amount”, “sum”,”«ContactID»=lvnumber”, “Donations”, true())
endif
endloop

Well, there are the curly quotes again.

To avoid this kind of autocorrection stuff, copy the code in the procedure window of Pan X with the menu command Source > Copy Indented Code, then paste it into your forum post. It should appear in the forum as indented preformatted text just like the following sample:

sample text as preformatted text.

Then your code would keep the quotes that you are using in Pan X.

(You can also invoke this format in the forum manually with 4 leading spaces.)

Kurt,
Thanks for the tip. I’ll do that from now on.

That menu command is in the 10.2 beta version, but I don’t think Will is a beta tester. This is the menu in 10.2

And this is the menu in 10.1.2

Using version 10.1.2, you would use Shift Selection Right after highlighting the text, and then copy. You can then use Shift Selection Left to put it back where it was.