Grabdata( in Pan 6 v PanX


#1

In Pan 6 the following produces the expected list. In PanX it produces an empty list:

thisYear=datepattern(today(),“YYYY”)

lvTheList=str(thisYear)[3,4]+¬+str(val(thisYear)-1)[3,4]+¬+str(val(thisYear)-2)[3,4]+¬+str(val(thisYear)-3)[3,4]+¬+str(val(thisYear)-4)[3,4]+¬+str(val(thisYear)-5)[3,4]

arrayselectedbuild lvArList,¶,"",?(grabdata("",array(lvTheList,2,¬))≥350,str(«memno»),"")

displaydata lvArList stop

The year fields in Pan6 are numeric 2 Digit, in PanX they are Float. Could this be messing up the calculations? I did try setting the output pattern in the PanX fields to “#.##” but still didn’t work. Or does the fact the fields are named with numbers cause a different result in PanX?


#2

FYI, when posting code to this forum, it’s much more readable if you indent it by 4 spaces. This causes the forum to format the text as code, like this.

thisYear=datepattern(today(),“YYYY”)
lvTheList=str(thisYear)[3,4]+¬+str(val(thisYear)-1)[3,4]+¬+str(val(thisYear)-2)[3,4]+¬+str(val(thisYear)-3)[3,4]+¬+str(val(thisYear)-4)[3,4]+¬+str(val(thisYear)-5)[3,4]
arrayselectedbuild lvArList,¶,"",?(grabdata("",array(lvTheList,2,¬))≥350,str(«memno»),"")
displaydata lvArList stop

Yes, I think that might be the problem. If the parameter is numeric, Panorama X assumes that it is a field number, not a name. This is documented:

However, I am a bit puzzled because I think even though your field names are numbers, the result of your array( function is text. So it seems to me like it should be interpreting it as a field name. There may be some other problem here, can’t say from the information provided.

As a general comment, though Panorama allows a field name to be a number (which is unusual, I don’t know of any other programming language that would allow this), I think it is a super bad idea that is sure to lead to confusion. Everyone knows that 2+2 is 4, but if you have a field name 2, you are likely to wind up scratching your head at some point. I would personally never use a field name that was just a number.


#3

Didn’t t know about the indent, will do so in the future.

It’s a very old DB that accumulates annual giving totals, essentially just a spreadsheet. I could easily create something new, but it is intertwined with lots of others, so you know how that goes. It seems to be working fine with the other DBs at this point, just gradata( fails.

The field names represent years, I’m curious what best practices would be if not name the fields by years. Just add a letter to the number to guarantee it is alpha-numeric? Like “18Y”?

Just tried:
message ?(grabdata("",array(lvTheList,1,¬))≥350,str(«memno»),“x”) stop

and it returns the correct result,as does fieldvalue( . So could it be something in arrayselectedbuild?

Update 2:
This returns the correct result
message fieldvalue("",1)
however this returns an array made up of only 1’s for all the records instead of the field value or memo. 1 is the value of the first record, first field

arrayselectedbuild lvArList,¶,"",?(fieldvalue("",1)≥350,str(«memno»),fieldvalue("",1))

It appears the calculation is not taking place properly.
If I switch to fieldvalue("",2), which for the first record is 0, I get an array of 0’s. If I try fieldvalue("",4), the value of which is 500, it returns and array of 500’s, etc.

Taking the calculation out of it with:
arrayselectedbuild lvArList,¶,"",fieldvalue("",4)
results in an array of 500’s, which again is the value of the 4 field in the first record.

So, does fieldvalue( not work in arrayselectedbuild?


#4

I’m a little confused about your field names themselves - and maybe Panorama is too. The statement above shows that you’re trying to get data from the fourth field but your words are indicating the 4 is the name of a field. fieldvalue( can be the name of a field, but if you do have 4 as a field name, how does Panorama know whether you want the 4th field or the “4” field? I’d expect it to go for the 4th field.

What if you try indicating the field name - if it is literally 1 or 4 - with chevrons?
arrayselectedbuild lvArList,¶,"",fieldvalue("",«4»)


#5

BTW, I use ArraySelectedBuild a lot and have not detected issues with it.

That is until now. Using FieldValue( in a test, I also got just a repeat of the first record’s value.


#6

Sorry, I see I should have put 4th and 1st fields, etc. But no, 4 is not the name of the field. Just trying to use field numbers to get around using field names.


#7

I don’t know if this is related or not but I very recently had a field in my checkbook file stop responding to an arraybuild. My Balance field returned an empty string for any arraybuild all of a sudden, even though it had always worked properly before. I set up a new field and tried to do a formulafill to copy all the cells in the Balance field to the newly added field and they too all came up blank. The new field was setup the same as the Balance field (Numeric(Float) with the output pattern set as “#.##”). I tried filling directly from the Morph icon as well as a procedure formulafill and nothing showed up. The problem persisted for a few days of opening and saving the file. At one point things finally went sideways and the Balance field itself completely went blank. I did a Revert to Saved when I closed the file and when I reopened it everything started working properly with the formulafill and arraybuilds for the Balance field all working again.

I have no idea what happened to cause the initial problem or what made it self-correct. I think maybe you can try adding a new temporary field and see if you can fill it from the problem field (assuming only one field is causing the problem). If more than one field is acting badly then this probably is not the issue.


#8

This issue is documented in Bitbucket issue #703, but since that issue has been resolved, it may have dropped off the radar.

A formulafill that uses a superlookup( with a grabdata( function back into the original database now works correctly. Note that this fix only applies to formulafill – other scanning operations like select, find, etc. still do not work with this combination, and will be a bit tricky to fix. Fixes issue #703 (again).