I’m trying to get an account holder’s name into my Clients database. Using the Relational Workshop, I get the formula to work. However, when I use the same formula for the Account Holder field, I get a failed to match error. See screenshot.
What is wrong?
There is no reason for this error to occur other than exactly what it states – there is no match. So in this case I would think that there is no matching client id number in these two databases. It has nothing to do with the Account Holder field, which only comes into play once a match is found. However, the screen shot doesn’t show client id numbers in the two databases.
I’ve attached another screenshot showing that the key ids are present in both databases. And, as shown in the former screenshot, the Relational Workshop finds the correct information. So why is the clients database field not working?
As an aside, I used a lookupall function for the Account Members and that works fine.
Perhaps there is extra whitespace in one of those values, an extra space or carriage return on the end would do it, or some sort of invisible character.
Hi Jim,
This morning I went to check your suggestion that the formula might be incorrect by having space or invisible characters in it. I got a shock when I opened my Clients database and found it empty, except for four records that cannot be seen. See 1st screenshot. I was able to retrieve a Time Machine backup and test that. It was strange in that the backups from yesterday were corrupted just like the one I originally opened. I had to go back a day or two to get one that was correct. It is also strange in that the corrupt file and the good file are at the exact same size, which seems to indicate that the records are there but just cannot be seen.
Once I got the Client database with all the records opened, I retested the formula. I used the Relational Workshop to recreate the formula and to ensure no extraneous characters were inserted, I used the copy button to past it into the formula field. I recalculated the field and got the same error as yesterday. See 2nd screenshot.
I did some further testing. I deleted the data in one of the Account Members fields. I then ran the Recalculate Field and the data was restored as expected. See 3rd screenshot.
At this point, I have no idea what is going on, although I suspect there is some corruption of the Clients database that may have caused the records to be hidden and the formula not to work. Looking forward to a resolution.
Thanks - Jim Weber
In your screenshots I see a not matching field name: The formula in the “Account Members” field of your Clients database is searching for data from a field “AcctMembrs” in the “Accounts” database, but the field in the “Accounts” database has the name “Accountt Members”.
There is also an AcountHolder in one of the formulas.
Coding is not kind to typos. Never has been, never will be.
In the database you are seeing the title of the field not the name. My understanding from the training is that the formulas use the account Name not the Title. See screenshot. And, as I indicated, the Relational Workshop gives the correct result. So, naming does not seem to be the issue, unless Panorama confuses Names and Titles.
In reopening the databases, I am now having another issue. The Clients data sheet will not open. Instead my Client Info form based on that database opens. I cannot get the data sheet to open even after quitting Panorama and reopening it.
Another stab in the dark: is the actual name of the field titled “Account Holder” in the Accounts database “AccountHolder”? I see in the Clients database the same field is named “AcctHolder”.
I think his field names are probably ok, there is a different error message if you specify a field that doesn’t exist in the database.
As for not being able to open the data sheet, you didn’t mention the View menu. Does that not work? Quitting Panorama and reopening it isn’t going to open the data sheet, but choosing Data Sheet from the View menu should do it. You can also open the data sheet by opening the Memory Usage window and clicking on the database name (this is handy if the database is open but has no open windows at all).
I was able to open the Clients database using Data Sheet in the View menu. I now recall that from the tutorials. I’ve covered a lot in the past couple of weeks and that I forgot. Thanks. I am curious though as to why the .Initialize procedure no longer opens the data sheet but opens the related form instead.
More importantly, the formula still does not work and the probable corruption issue remains. If you think it is helpful, I could create a new database from my CSV file.
Forgot to mention that the Clients database does not show up in the Open Recents menu. I don’t know if that means anything but it seems odd since some of the items in that menu have not been opened for couple of days.
The initialize only does what it directs to be done. If it directs that a Form or Window be opened, it will do that. If it does not direct that a form or window should open, then nothing will be done in that regard.
The windows that open will by default be the same windows that existed when the db was closed. (Except again if the .Initialize procedure said to do something different.)
Did you open the Clients db in a Secret window? If so, that would keep it from displaying in the Open Recent.
The Open Recent is a list of the most recent x number of dbs. It does not care if they were all opened 6 months ago.
When you have the database open, open the Database Options panel from the File menu, then make sure that the Exclude from Recent Menu option is NOT checked.
When I tried that, the panel did open; just got a spinning ball.
While that seems problamtic, I’m not nearly as concerned about that issue as the original issue, the formula not working in the field. I really could use some direction on that issue and the probable database file corruption,
When Jim responded and mentioned possible white space or other invisible characters, he meant the data, not your formula. Gary’s “Reveal Invisible Text.pandb” is a great help to find this kind of characters in the data.
Thanks for that clarification; I did think he was talking about the formula.
However, if there were an issue with the data in the key fields, then the related function, that I mentioned earlier that looks up all the account members, would not work, as it uses the same key fields to look upon those account members. As I indicated above, It works just fine.
After I force quit PanoramaX because it was not reponding, I re-opened the Clients database and check the Database Options. Indeed the Exclue… was checked. I unchecked it and it now appears in the menu. I have no idea how that got checked.
@jimweber sent in his files and the mystery that started this thread has been solved. I wanted to share the solution here in case someone else runs into this in the future.
The problem was that he was using the Fields>Morph>Recalculate Field command to run his formula. This runs the formula over and over again for every visible record in the database, not just the current record. Though the key matched for the currently selected record, he had hundreds of records in his database with key values that did not match (most were simply blank). So naturally this caused the “failed to match” error to appear.
The solution is to add a default value parameter to the lookup( function. In this case the natural default is probably empty text: "". So the needed formula is:
lookup("Accounts",«fkAcctHolderId»,«fkAcctHolderId»,«AcountHolder»,"")
This clears up the problem and the field can be recalculated without any error message.