After using the Relational Workshop the results appeared to be correct when matches did occur. I did have a red thumbs down when a match did not happen as I tested the lookup by clicking on different records in the recipient db sample view. I copied the formula to the clipboard.
Then with the recipient db active, with ‘LastVerifyDt’ (a date field), being the active field (and the receiving field,) I chose ‘Field/Morph/Fill with Formula’. I pasted the formula from the clipboard, pressed the Fill button and NSNotify said: ‘Lookup( function failed to match.’
231225.Events is the target database which was still open
«sponsorID» is a field in the target database
«ClubID» is a field in the recipient database
«created» is a date field in the target database
Resolved:
There were two choices in the Wizard for when the requested data is missing, ‘Error’ and ‘Today’ (this was a date field). I really wanted to be able to choose ‘Empty Text’ but that was not an option. When I later chose ‘Today’, the FormulaFill worked. Fortunately in this situation, there were no ‘today’ values in target db so I was then able to reliably select all ‘today’ values that had been filled in the recipient db and do a Field/Morph/Clear Field to remove the erroneous data.
When you do a fill based on a formula you must supply a formula that will calculate a value for every record in the database. In the case of a formula with the lookup( function, you generally need to supply a default value unless you are absolutely sure that the lookup( will succeed for every record. It’s up to you to decide what a reasonable default value is.
Well sure, because Empty Text is not a valid value for a date.
The Relational Workshop is a helper to assist with the most common use cases. It is by no means a comprehensive programming tool that will provide for all possible solutions, and it definitely won’t help with use cases where the solution requires more than just a lookup( function. So yes, for your application, I agree that the wizard is not “smart enough”. It’s not claimed that it is smart enough to solve any problem. In this case, you would probably want to manually code the formula as:
Of course you could start with the Relational Workshop to build most of this formula, and then make the minor changes necessary for your desired outcome.
By the way, doing a fill with a lookup( is definitely not “best practice” in Panorama 10.2. Using the join feature will do the same job but orders of magnitude faster.
When I create a field with a Date type and there is no entry in that field for a record, is that not an empty value? I did not want «created». I wanted nothing. What is used for the ‘default’ when we want nothing in a date field?
A lookup was used for this case as it was a one time issue never needing to be used again and was easily quick enough.
By the way, doing a fill with a lookup( is definitely not “best practice” in Panorama 10.2. Using the join feature will do the same job but orders of magnitude faster.
To emphasize that, I had a project with tens of thousands of records and the lookups from a long list to a long list took hours and hours. When I used the join method, it was over in less than 5 seconds.
In a formula, there is no such thing as an “empty” numeric value. So for example, a variable cannot hold an empty numeric value, only zero.
Database fields have an extra capability to register “empty” as a value separate from zero. The empty value is treated as zero in calculations, but the zero is not displayed in the data sheet or forms. So essentially there is an extra flag that tells Panorama not to display the value. But formulas know nothing about this extra flag – all they see is zero. And there is no such thing as an “empty” numeric value that can be used as a default value, only zero.
The zeroblank( function is a special case. It always returns the same value it is passed. However, if the value passed to it is zero, and the formula is used to assign a value to a field, it will reach out “on the side” and set the extra flag that tells Panorama not to display the value. If the result is assigned to a variable or displayed, the zeroblank( function has no effect.
Since the zeroblank( function only works in specific situations, I don’t think it’s appropriate to build special logic into the Relational Workshop window to generate this function as part of the formula. Of course, that’s a judgement call, I’m sure some may disagree.
What I would like to see is the ability to choose the ‘Zero’ option for the default value in a lookup such as this. The choice is there in the popdown but it is grayed out. I will be happy with putting a zero value in the date field. No special logic is necessary.
