Lookup/lookupall issues

I’m trying to import a field full of dataarrays into a database without much success. The code:
field “PlayerStats”
formulafill lookupall(“Players”,RecordID,RecordID,PlayerStats,cr())

puts what looks like dataarrays into the PlayerStats field but when looking at it with a displaydata exportdataarray it is totally blank.

if I use:
field “PlayerStats”
PlayerStats = lookup (“Players”,RecordID,RecordID,PlayerStats)

It gets and shows me the corect data.

I would like to figure out what I am doing wrong here.
Any help is much appreciated.

The lookupall( function will lookup every value where the value in the key field equals the key value, and make a text array of those values. If there is only one matching record for each key value, then I think you want

field "PlayerStats"
formulafill lookup("Players",RecordID,RecordID,PlayerStats)

I’m not sure what exactly is happening with your code. Lookupall( returns a text datatype, but data arrays have a binary datatype. There is bound to be some confusion there.

As David stated, the lookupall( function generates a text array, not a data array. You say the result “looks like dataarrays”, but data arrays are binary values that can’t be viewed at all.

There is a function that will scan a database for matching information and create a data array, the dataarraybuild( function. I started to try to write a version of your program using this function, but then I decided I really didn’t understand what your program was trying to do. Is PlayerStats a binary field that already contains data arrays you have built some other way? In that case, it sounds like you are wanting to combine multiple data arrays into a single data array, perhaps by appending them? Since you are using lookupall(, are you expecting there to be multiple records with the same RecordID that you want to combine? If so, I think there is no quick way to do that, you would have to go record-by-record and use the dataarrayappend( function to append each array into a combined array. That’s going to be quite slow if your database is a signifcant size. Hmm, you could use dataarraybuild(, which is going to give you a data array of data arrays. Then you would have to use a loop to create the actual combined data array I think you are looking for. So that would be a bit faster but you still can’t use formulafill.

Ok, I think I see where you were saying in the first place about “looking like a data array” - you simply meant it “looks like gobble-de-gook” right? The lookupall( function should perhaps return an error if you pass it a binary field. Instead it is simply appending the binary data, but you can’t combine data arrays by simply appending the binary data. (If you could, you could simply append data arrays by saying a+b instead of having to use the dataarrayappend( function.). So yes, you got some binary gobble-de-gook, but it isn’t binary gobble-de-gook that is actually a data array.

I’ve created an entry in BitBucket for this. However, on further reflection I’m not sure about this. The lookupall( function will definitely not work with data arrays or dictionaries, but there are situations where simply appending binary data might be useful. So I’ve marked this as a proposal rather than a bug report. In the meantime, definitely don’t use lookupall( for data arrays or dictionaries.