I am wanting to pull in selected records from a 2nd databse into a Text List in the primary database. In the Query Box for the Text List Options on the1st db, I am thinking that a ««LocationCode»» = «LocationCode» should work if the Database is set to the remote database. But nope. I was going to try
?(««LocationCode»» = «LocationCode»,ExportLine(),"")
A simple ExportLine() pulls in the current record as many times as there are records which makes sense but I want a specifc selection of record’s data.
Using the Relational Workshop, I can see the records and data that I want with the formula:
arraybuild(cr(),“Activity Log”,|||datepattern(Date,“MM/DD/YY”)+tab()+Time+tab()+User+tab()+Action+tab()+Purpose+tab()+Result+tab()+Unknown|||,|||««LocationCode»» = LocationCode|||)+ignore("",LocationCode)
but I can not see how to use that array as my datasource for the Text List. I haven’t been able to find anything in the Help with any example that would assist.
Text List objects do not support the double chevron syntax.
If you have set the Text List to scan another database, then all fields included in the query formula must be fields in the target database. You cannot directly reference fields in the current database. If you can get the values you want to search for into a variable, that would be one solution. Another would be to use the fieldvalue( function to peek back into the current database. (In fact, in situation where the double chevron syntax does work, Panorama actually converts the chevrons into a fieldvalue( function.)
I think you might be able to leave the database specification as "" instead of including the name of the current database, but I’m not 100% sure about that. Panorama might consider the database being scanned to be the target database at the time this formula is likely, in fact I think that is likely. So if that’s true, you will have to explicitly include the database name in the formula.
You could also use your arraybuild( formula. To do that, leave the Database and Query options blank, and put the formula into the Formula panel. You can use ANY formula as a data source for a Text List. The Database and Query options simply provide a convenient shortcut that makes the most common operations simpler to create.
Using the Relational Workshop to easily accomplish the arrayBuild that I already had and to simply empty the Database field.
I had imagined that the Database field needed to know where the source was. My arraryBuild did already indicate where the data was but I thought redundancy would be a good thing. I’m not really clear as to why the Database field needed to be empty for this to work, but alas, I am happy now.
This would be a convenient moment to perhaps suggest that Panorama X could assist the user when they are checking the ‘Database Navigator’ checkbox, that Panorama X is going to empty the Value entry (or perhaps ‘dim’ it.) That misleading item, and this hole where having to have the Database field empty for my needs to be achieved were not intuitive so anywhere that Panorama X can assist is always appreciated.
If the Database option is empty, the Text List simply evaluates the formula and displays the result.
If the Database option is not empty, the Text List scans the specified database, evaluating the formula once for each record in the specified database. An advantage of this method is that the Text List will update the display as the scan is in progress. If you use the arraybuild( method, the display will not update until the entire arraybuild( operation is complete.
Essentially, the Text List object has the arraybuild function built into it, and this is activated when you specify a database. The database, formula and query parameters of the arraybuild( statement correspond to the same options in the Text List object. However, there is one difference in that currently the Text List object does not support the double chevron trick.
Hearing that I might be able to get a faster result, I am going to put aside the fact that clearing the database does work. I really want to understand what is happening here.
I’ve re-entered the database name into the Database field. I’ve elminated the variable issue by hard coding the KeyData for the moment. The Query is now:
Because the Query is supposed to be running this for every record in the Activity db, I am expecting that I will only get 1 record back as there is only 1 record with the LocationCode of ‘OR:45426’ in the Activity db.
But I am now back to getting the correct data, repeated as many times as there are records in the database.
If LookupAll is acceptable for speed, I’ll actually use the very cool, LookupAllSextet( to get the 6 fields of data.
How do I get just the single record only once using the Query box for maximum speed?
This will return TRUE for records in the “Activity Log” that have the same LocationCode as the current record in the database that contains the Text List (what I am calling the “primary database”). In my formula above, you need to change "Primary Database" to the actual name of the database that contains the Text List object. I would have put that in the code, but I don’t know the name of that database.
