My databases seem to be related properly, it worked before, why doesn't anything populate when I create a new record in the second database and try to relookup to get data into it? Help!
It's not clear why you would need to re-lookup in the first place.
We can't see your files and you haven't told us how you have designed them so we can't do much more than make fairly general guesses.
Looked up value fails when:The value in the match field doesn't match to any record in the related table.The relationship has changed in some way such as matching to an unidexed (often global) field.
The relationship refers to data in another file and the external data source reference is not working, maybe due to renaming or moving the file (or a folder that encloses it.)
Re-Lookup can fail if you do this manually and don't have the focus (cursor) in the correct match field. Or due to the reasons listed above.
I suppose that your file could also be damaged, but we'd need to rule out the other possible factors first.
Gosh, thanks. So many of these things could be right.
1) I copied the key field in the first table, went to the second (related) table and created a record, pasted the key into the key(id) field, tried to relookup to fill in the lookup fields, so the values must match. Maybe this sounds stupid to you, but this is how I add records to my second database, one by one. (I'm sure there is a better way, I just don't know it.)
2) Important note! Since iCloud went down this week, I moved both files to a local hard drive, and had to re-relate them! I'll bet I did something wrong... what could it be? I went back and tried re-relating the bases, from both sides. I thought I had it right. Maybe it isn't, but there seem to be links between the two fields.
3) It isn't failing because my cursor isn't in the match field, I got that part right anyway.
Thank you so much for your semi-blind advice -
If you described more about your design, it wouldn't need to be so "blind".
What you describe, though far from ideal, should not require Re-lookup as the data should look up automatically after you paste in the match field value. The fact that it didn't, even when it was 'working" for you suggests that you have something else going on here such as a second relationship that also looks up data needing that "re-lookup" action.
Your files shouldn't have been opened and used from icloud in the first place, but yes, if you have more than one physical file, then moving the files to a new location may have changed either a file name or the "path" used by one file to find and connect to the other. You'd need to open Manage | External Data Sources and take a look to see if there are any broken references.
You can also spot these in your relationship graph as they Table Occurrence boxes to a external data source that cannot be accessed will fail to list any fields.
Hmmm. What can I tell you about the design? I have two files, related by one field each. I moved them to a hard drive, then went to Manage | External Data Sources and the references were broken so I re-set them. I'm not very good at this, so I don't know what I've done wrong all this time that it doesn't look up automatically, it never has.
I'm afraid "your relationship graph as they Table Occurrence boxes to a external data source that cannot be accessed will fail to list any fields" doesn't mean anything to me, sorry. Where do I see this?
Thank you for your patience with me -
Could it be when I moved the tables I needed to update all the calculations and lookups of the fields themselves to point to the new database?
Yes, indeed, examining the fields themselves they needed redefining after being moved, there were no pointers to the newly moved database.
However, this doesn't answer why I always need to re-lookup when I create a new record. Must be in my awkward design?
OK, progress has been made *but*
"Your files shouldn't have been opened and used from icloud in the first place, but yes, if you have more than one physical file, then moving the files to a new location may have changed either a file name or the "path" used by one file to find and connect to the other."
Now that I don't use iCloud I have a new problem: When I transfer from the MacBook to the iPad to take inventory, it makes a copy. When I re-uploaded back to iCloud, it let me "replace" the original file, all links and relationships preserved. Now it uploads a new file to my hard drive. Pffft. No relationships, apparently. Reconstructing them is painful, and I go out to the iPad often every day.
What do I do to preserve the relationships and field definitions?
What can I tell you about the design?
Screenshots of your relationship graph, and perhaps the manage external data source dialog would show a lot. There's a picture tool in the tool bar of the forum reply box that enables you to insert pictures into your replies.
Simply moving the files from one location to another should not break the individual calculations.They may show up as not working because the external data source reference isn't working, but to correct it, you change the external data source reference instead of redefining the field's options. Moving files might break external data source references, particularly if they are not set up to use relative paths, even then only if the move renames files.
Using iCloud, (or drop box or google drive, or ...) to copy files back and forth between iOS device and computer is fine and when you do that, you have the option to replace the older version with the newer copy that you are copying over. This should keep file names from changing.
If you had all of your tables inside a single file, this would also be simpler to manage and would avoid this issue with file name changes breaking your data source references.
Retrieving data ...