Possible bug? in arraybuild code

Mdunlop · January 28, 2023, 4:42am

arraybuild dFound,“/”,“Cattle Database-Active”,Unique,(Check="Y”)

This code works in the following programme (sorry its so long, but I think you need it all to figure out what is happening and/or reproduce it):

local dListID,dListLetterID,dFind,dFound, dfoundLetterID,dListNumID,dfoundNumID,dResult
arraybuild dListID, "/", "Import Tag Data", ID
let n = arraysize(dListID,"/")
let dListLetterID=""
let dListNumID = ""
looparray dListID,"/",dFind
    dListLetterID = arrayinsert(dListLetterID,1, 1, "/")
    arraybuild dfoundLetterID, "/", "Cattle Database-Active", Unique,TotalID contains dFind
    dListLetterID = arraychange(dListLetterID, dfoundLetterID, 1, "/")
    dListNumID = arrayinsert(dListNumID,1, 1, "/")
    arraybuild dfoundNumID, "/", "Cattle Database-Active", Unique,ID = val(striptonum(dFind))
    dListNumID = arraychange(dListNumID, dfoundNumID, 1, "/")
endloop
arrayboth dListLetterID,dListNumID,"/",dResult
arraystrip dResult,"/"
setactivedatabase "Cattle Database-Active"
looparray dResult,"/",dFind
    find Unique = val(dFind)
    Check = "Y"
endloop
arraybuild dFound,"/","Cattle Database-Active",Unique,(Check="Y")
if n < arraysize(dFound,"/")
    Message "Extra records selected compared to the original data. Please check for duplicate ID"
endif
if n > arraysize(dFound,"/")
    Message "Not all data records have been located in this database. Please double check data"
endif
openform "SearchList", "database","Cattle Database-Active"
selectSearch = "Y"
showvariables selectSearch

But if I add a single line of extra code, it doesn’t work.

local dListID,dListLetterID,dFind,dFound, dfoundLetterID,dListNumID,dfoundNumID,dResult
arraybuild dListID, "/", "Import Tag Data", ID
let n = arraysize(dListID,"/")
let dListLetterID=""
let dListNumID = ""
looparray dListID,"/",dFind
    dListLetterID = arrayinsert(dListLetterID,1, 1, "/")
    arraybuild dfoundLetterID, "/", "Cattle Database-Active", Unique,TotalID contains dFind
    dListLetterID = arraychange(dListLetterID, dfoundLetterID, 1, "/")
    dListNumID = arrayinsert(dListNumID,1, 1, "/")
    arraybuild dfoundNumID, "/", "Cattle Database-Active", Unique,ID = val(striptonum(dFind))
    dListNumID = arraychange(dListNumID, dfoundNumID, 1, "/")
endloop
arrayboth dListLetterID,dListNumID,"/",dResult
arraystrip dResult,"/"
setactivedatabase "Cattle Database-Active"
looparray dResult,"/",dFind
    find Unique = val(dFind)
    Check = "Y"
    Worknotes = superlookup("Import Tag Data",|||««TotalID»» contains ID|||,|||Comments|||)
endloop
arraybuild dFound,"/","Cattle Database-Active",Unique,(Check="Y")
if n < arraysize(dFound,"/")
    Message "Extra records selected compared to the original data. Please check for duplicate ID"
endif
if n > arraysize(dFound,"/")
    Message "Not all data records have been located in this database. Please double check data"
endif
openform "SearchList", "database","Cattle Database-Active"
selectSearch = "Y"
showvariables selectSearch

I receive the following (mysterious) error:

which I don’t understand because I don’t think I’ve referred to a variable ‘e’

It doesn’t appear to be a problem with the extra line,
Worknotes = superlookup(“Import Tag Data”,|||««TotalID»» contains ID|||,|||Comments|||)
because it has successfully done what I was asking it to do, before the procedure stops. Any help or explanations or work arounds gratefully accepted.

admin · January 28, 2023, 6:03am

You’ve really made it difficult for anyone to help you.

First of all, I don’t know how you pasted the code into the message so that each line is surrounded by asterisks, but that makes it almost impossible to read here in the forum. Please use the technique described below in the future:

First select the text you want to copy
Next, choose Copy Indented Code from the Source menu.
Finally, paste into the forum window.

You’ll see that this formats the text nicely here in the forum. It also makes it possible for someone to copy the code from the forum so that we can try it out without having to retype it all over again.

Secondly, you give us two long sections of code and say that you added one line. It would be really helpful if you told us what the new line is, so that we don’t have to search to try to figure it out. In fact, I could not figure it out.

Thirdly, that is a runtime error. In other words, it’s not a syntax error, but an error that occurs when you run the program. Your code contains 3 arraybuild statements (I think). If you click on the error message, Panorama will show you which one is causing the error. That could be really helpful in tracking down this needle in a haystack.

I did do some tests with arraybuild to see how it handled formulas with incorrect field names, and it seems to report the error correctly. So I don’t think it’s a bug in arraybuild, I think there is a problem with your code. But I can’t see what it is.

Mdunlop · January 28, 2023, 6:07am

So sorry. I’ll retry. I saw it presented strangely, I used italics, but couldn’t fix it up.

Mdunlop · January 28, 2023, 6:12am

The arraybuild at the top of my post appears to be the one causing the error. It is the 10th row from the bottom of the programme. The extra line is 2 above it. I’ve played around with where I put that line of code, including running another loop, and it doesn’t seem to make any difference.

admin · January 28, 2023, 7:09am

Thank you, that is MUCH clearer. The new line is the superlookup( function, right?

Unfortunately, I still don’t see the problem. Maybe there is a bug.

I wonder if the superlookup( is messing with the setactivedatabase. Try adding another setactivedatabase right after the superlookup( line. OR - use topdatawindow instead of setactivedatabase. Maybe the “secret” window is causing a problem here.

Another thing you could try is rewriting the arraybuild as a function, like this:

dFound = arraybuild("/","Cattle Database-Active",|||Unique|||,|||Check="Y"|||)

It shouldn’t make any difference, but I think it’s worth a try.

Another thought – you could set up a relation between “Cattle Database-Active” and “Import Tag Data” and use the related( function instead. I’m assuming the the TotalID field really contains just one ID. If that’s not true you’ll have to stick with superlookup(.

Another thing you could try, change the superlookup( to

Worknotes = superlookup("Import Tag Data",quoted(TotalID)+||| contains ID|||,|||Comments|||)

If that works, there is a problem with double chevrons.

It’s quite late here in California, so I may not see your reply for a day or two. Hopefully at least one of these suggestions will help.

Mdunlop · January 28, 2023, 9:32am

Thanks Jim.

Try adding another setactivedatabase right after the superlookup - didn’t make any difference.

dFound = arraybuild(“/”,“Cattle Database-Active”,|||Unique|||,|||Check=“Y”|||) - worked perfectly

Worknotes = superlookup(“Import Tag Data”,quoted(TotalID)+||| contains ID|||,|||Comments|||) - also worked perfectly