I have a database, Workshop Attendees, that tracks everyone who attends our various workshops. Sometimes I want to select all the records in the Mail List database that match the records I’ve selected in the Workshop Attendees database. I used to do it this way, which takes 6 seconds and selects the right records:
Select lookupselected("Workshop Attendees","Mail List Record Number",«Record Number»,"Mail List Record Number",0,0) ≠ 0
Now I tried to do it using related(, and this took about 3 minutes and didn’t change the current selection. Any ideas what I’m missing?
Select catcherror(9999999999999,related(“Mail List Record Number”, “Workshop Attendees”)) = «Record Number»
The code above is probably not any faster than using a lookup. I believe there is a trick way that you could make this significantly faster in Panorama X 10.2, using a couple of new features I haven’t mentioned in the class. Here is the code (hopefully I am understanding the field structure in your databases):
globaldictionarybuild "tempRecordNumbers","Workshop Attendees",
«Mail List Record Number»,true()
select catcherror(false(),
globaldictionaryvalue("tempRecordNumbers",«Record Number»))
deleteglobaldictionaryvalues "tempRecordNumbers"
Let me know how this works for you. If it is much faster as I believe, I could make a new statement that would be very simple to use, something like:
selectrecordsrelatedto "database"
In the meantime, here is the help for the new functions I am using in this code:
Secret insiders note – the join statement has the equivalent of the globaldictionarybuild statement built into it.
The first version works fine, but it isn’t any faster than the lookupselected(. It produced 98 records selected out of my 13146, which is correct.
The second version is blazing fast, basically instantaneous. It selected all 98 records, but it selected additional records as well, totaling 2506 records out of 13146. Perhaps that has something to do with my settings in the Relations panel.
Having a statement like selectrecordsrelatedto would be awesome!
My example code using the globaldictionary doesn’t actually use the settings in your Relations panel, so that’s not the problem. Most likely I didn’t quite understand your field arrangement correctly. Or maybe I just made a mistake.
Sleeping on it, I think the selectrecordsrelatedto is definitely a good idea, and probably would not be a huge project. I’ve added it to Bitbucket so I won’t forget about it.
Actually, I think I figured out the problem. globaldictionarybuild scanned the entire database, and I wanted only the selected records. So I think it works fine. I guess I just have to temporarily delete the unwanted records from the database, unless there’s a globaldictionaryselectedbuild command somewhere.
Yep, in fact there is! It’s the same statement, but you have to add another parameter, a formula that specifies a subset. In your case, change the first line to:
globaldictionarybuild "tempRecordNumbers","Workshop Attendees",
«Mail List Record Number»,true(),info("visible")
In other words, add ,info("visible") on the end of the line.
Jim,
Consider another application for this idea, data validation. One of clairvoyance’s virtues is encouraging consistent data entry. But it doesn’t stop you from intentionally, or sometimes accidentally, inputting data that didn’t match. Is it practical to test whether a just input value already existed in a reference database or give an error if not? X.2 offers new data validation tools, something like this might be another option.
Figured out answer to my own question. It’s not needed. I can use lookup to check whether a given value is in a given field with no match set as error. and use if error to handle new values. Cool! Plenty fast for human, one at a time, data entry speeds. Checking lots of records for matches in one operation is where the speed advantage piles up.
Today I implemented the selectrecordsrelatedto statement, and I figured out some applications for it. This was a very cool idea – thanks! Here is the documentation, soon you’ll have this feature in your hands.
)