Arraybuild() from 2 or more databases


#1

I am wondering if there is a way to use arraybuild() to grab data from 2 or more databases. A simple example would be to build an array from a “company” and “contacts” database. If each database shares a field named “companycode” that contains a unique value for the company and links the contacts with that company. the end result would look something like this.

CompanyName,ContactName
Provue,Jim Rea
MPI,Steve Middleton
etc.

Doable? Any performance issues?


#2

I’m not positive I know exactly what you need done, but here is something you might use as a start. This is assuming you have one database named Database1 with the the company name in a field named CompanyName along with another database named Database2 with contacts in a field named ContactName. There is also a field in both databases named companycode with the unique code for each company.

arrayfilter(arraybuild(¶,"Database1","companycode"),¶,|||superlookup("Database1",{companycode=import()},"CompanyName")+","+superlookup("Database2",{companycode=import()},"ContactName")}|||)

This is setup as a formula using the arrayfilter( functiion but could easily be written as a statement using the arrayfilter statement instead. This just uses arraybuild( to make an array of the company codes and then uses arrayfilter( with two superlookup( functions to gather the corresponding info from the two files. No idea how fast this would be in execution so you would have to test that at your end.

I have not check this in actual practice so be forewarned (list of legal disclaimers to follow. :upside_down_face:)


#3

Thank you Gary I will give it a try with noted disclaimers. :), Quick question, would using gradata() be another possible approach? I tried finding info on gradata() in PanX help file but other than being mentioned in superlookup() I can’t find anything.


#4

When grabdata( is used to grab data from another database, it grabs it from whatever record is active in that database. It doesn’t have access to any of the other records. To link values from different databases that share a common key field, you need a lookup( of some kind.


#5

There is nothing in the Help file on grabdata( but it is certainly supported in Panorama X. If you click on the link to grabdata( shown in the superlookup( Help file it takes you the “Under Construction” page.


#6

The grabdata( function is now called fieldvalue(. But grabdata( will still work. But Dave is correct, fieldvalue( (or grabdata( won’t do what you want. You have to use a lookup function.


#7

I found a “function_grabdata.html” file within the application bundle, so the documentation exists. There must be something wrong with the link.


#8

There is no entry for grabdata( in the Help file data sheet. Is this where the actual link would be taken from in the Filename field?


#9

It is easy to correct the existing link in the file “function_superlookup.html”. Replace “under construction.html” with “function_grabdata.html”.

But there is no record for the grabdata( function in the Help database.

The shellopendocument statement is completely undocumented.


#10

The function_grabdata.html file is there because that is what the function was named until a while ago. So it was originally documented under that name. That file is a leftover, but there are no links to it because the entry in the Help database has changed. Some statements and functions have more than one name, but there can be only one primary name that is linked to. You can find grabdata( if you turn on the Full Text Search option, because the fact that this function was originally called grabdata( is mentioned in the History section of the fieldvalue( page.

The shellopendocument statement is now called openurl. Again, shellopendocument will still work, but that is no longer the primary name. This change was made right from the start in Panorama X, so there is no leftover statement_shellopendocument.html file.

Since Panorama X is such a complete makeover, it seemed a good time to fix some of the awkward terminology that Panorama has used in the past. So I think fieldvalue( is much clearer than grabdata(, and openurl is clearer then shellopendocument. For compatibility with your existing programs, the old terminology almost always still works, but it is no longer listed under that name in the Help. However, you should always find this listed in the History section of the page.