I’ve done this before under Pan6, but can’t seem to get it to work in PanX: I have two related DBs. There are people who I’m entering in DB2 who can be looked up in DB1, and then info plugs in where it should. But if the person is not in DB1, I need to be able to enter their info in DB2. However, the lookup function is deleting hand-entered data as soon as I hit tab.
Well, that isn’t entirely accurate. The lookup is based on the email address, which then finds a form# and first and last names. If the person is not in DB1, then I need to put in a different form# (actually from the prior year’s DB, but it is hand entered) and their first and last names. When I paste the email address in, and hit tab, it wipes out the number I typed in Field1 (the form#) and goes to the firstname field; I enter the name, hit tab, and it goes to the latname field – but it does not wipe out the firstname! However, when I hit tab from the lastname field, it does wipe out the last name. I then have a name field which combines first and last. Now, if I go back to the email field and hit tab, it does wipe out the first name.
Simply put, is there a way to set it up so that lookup will not delete hand-entered data when the lookup “fails”?
Both lookup( and superlookup( include optional parameters for default values in PanX. In Pan6 (which only had the former) I’d put the name of the field I was filling there when I wanted not matching to leave the prior value rather than erase it. And I think Pan6 allowed formulas, including further lookups, to be embedded within that default value slot.
I’m not sure whether fields/variables/formulas are acceptable as default values in PanX. When I started converting only fixed values worked there so I coded otherwise. Since then Jim has fixed most of my then lookup bug complaints (I think one remains) so my Pan6 way might work now. I’d try that first as it’s simpler. If you can’t get it to work then a multistep workaround would be needed.
One option would be to specify an unique, otherwise nonsensical, default value. Then select records with that value either with another lookup from an appropriate database or from a local variable hosting a pre-built text array with your previous values.
Alternatively, first select those records that do match—ie. your lookup doesn’t give your default value. That’s doable in one step with lookup(, but requires extra steps and temporary fields to accomplish with superlookup( and so is listed on the remaining bug list. Next do your formulafill lookup knowing nothing desired will be erased by it. If you want to not just leave the pre-existing values for the non-matches, then selectreverse and do your other lookup (if this started with less than all selected you’d need a temporary field to mark that starting selection rather than just select reverse.)
Yes, any formula can be used as the default value. I just did a test to make sure. So the multistep workaround is not necessary.
I don’t remember any bug that required only fixed values. It would be kind of difficult to make it work with only fixed values instead of a formula, it seems very unlikely that this could happen by accident (because all functions handle parameters using the same code, so this parameter is calculated the same way that, for example, the parameter to the upper( statement is calculated). I don’t have any recollection of a bug like this. In any case, it definitely works with a formula now.
One point to keep in mind, if the default value is a formula, any fields in that formula are calculated based on the CURRENT database, NOT the database that is being looked up into. Panorama 6 was the same. For this application of leaving in the prior value, that is exactly what you want.