There's a simple way to do this that should work for you.
In each case where your layout is based on tblSow and shows the tblSow::SourceID field, double-click it while in layout mode and link this field's text box to tblSource::SourceID instead. Now, everytime you update the SourceID field in tblSource, all your layouts should automatically update the value.
This question concerns me a bit: "Should I restructure my tables so there isn't repeating fields? "
I don't see any fields in your first post specifically identified as repeating fields. (I'm assuming you mean a field defined with multiple repetitions in its field options.) I don't see a reason why any of the fields you describe would be defined in that manner. Thus, I can't tell how this detail will affect my suggestion.
Sorry I didn't get back to you sooner on this. Your suggestion makes perfect sense, the problem is I want to keep all the records from all the seed lots, not "update" the Source field every time. For example if I have seed "xyz" received on 12/24/09 with SourceID 1234, and I got a new batch of xyz on 2/15/10 with source ID 2354 it would look like this within the table
Name DateReceived Source ID
xyz 12/24/09 1234
xyz 2/15/10 2534
Does that make sense? So how would I get the "current" Source ID? If I plant a set on 1/15/10 I want SourceID 1234 to show up in the SourceID field in tblSow.
No problem. This is a problem very similar to handling price changes in an invoicing system. You want the most recent data to appear in all new records, but don't want anything to change for existing records.
I'd use a looked up value auto-enter field option for this and set the relationship so that the matching entry with the most recent date is "first" in the list of matching records.
Define a new relationship linking your two tables or update an existing relationship. (You'll have to figure out if this relationship change affects any other part of your system.)
Define a sort order for the relationship (double-click the line linking the two table occurrences) and specify that the records in tblSource are sorted by date in descending order.
Then your SourceID field in tblSow should be set to "look up" the source ID value via this relationship.
Ok I tried this, and I put in 2 records for xyz, one of sow date 12/29/09 and one of 3/25/10. Basing this off the example I gave above, and after altering the relationship to sort by descending order for "received date". They both returned a value of 2534 in ::tblSourceID field in the layout. It seemed like it grabbed the most recent Source ID from tblSource, even though one of the dates (12/29/09) is from the other seed lot time range. Any ideas? BTW, the tables are linked by "Name" since that is the only common unique ID, not sure if that matters.
Should I add a field for "EndDate" in tblSource, and have ::tblSourceID be a calculation that goes off a range of dates? Or something similar?
We may be picturing slightly different relationships or how it is used.
I'm picturing this:
tblSow::Variety = tblSource::VarietyID (set relationship to sort by DateStarted in descending order.)
Put tblSow::SourceID on all your tblSow based layouts.
Define a looked up value option for tblSow::SourceID to to use the above relationship to look up tblSource::SourceID each time a valid VarietyID is entered/selected for tblSow::Variety on the same record.
Existing tblSow records will be unchanged, but when you create a new tblSow record and enter/select a variety, the most recent SourceID value for that variety should be copied into this field and thus appear on your layout auotmatically.
Is this what you want?
Can you get it to work?
That is exactly how I have it set-up, and I think that will work. The problem is I am making up fake records right now, and no matter what sow date I put in there, the most recent SourceID will show up. I just tested it with a new "name" and the correct SourceID came up for it. So basically entering fake data for March or December won't show me anything, because the field is based off the most "current" SourceID for that Name. Nothing to do with date ranges, so it is fine fine. Thanks :)
One other question, this is from my original question in this thread...
"Oh, also, I have one other table that is a stand alone table:
tblInventory (current needs of all seed)
This is updated when new projects come along, and shows the current needs for all varieties of seeds in-house. So far it is a stand alone table, but is there a reason to link it to the other tables? I am trying to figure out a use."
Any ideas here? Or maybe leave it as a stand alone table? This is basically telling us what needs to be planted each week, and why. Separate from the tblSow (current planting) and tblSource (source material)
Thanks again Phil
Uh oh, I think I spoke too soon Phil. I just created some more mock records, and across the board the tblSource:SourceID field on the tblSow layout was overwritten with the new ID number, even on existing records. I am doing something incorrectly? hmmm. sorry to be a pain
I can certainly imagine linking this table to others in your system by variety to make certain reports possible--that seems to be the common link in your data, but I'm not you and don't know your business. In any case you can link the table regardless of whether it's part of the same file or in its own.
Tables in external files work darn near the same as tables in the same file. Thats because filemaker originally was limited to one table per file and we developers had tons of different files in our solution in order to support a multiple table solution. We griped and complained and begged and pleaded and finally filemaker made multi-table files possible. Now that we got what we wanted, we find that putting all your tables in one file isn't always best. :smileywink:
Advantages to putting all your tables in one file:
- If you set up accounts and privileges to limit how users can interact with your data, you only have to do this once. With multi-file solutions you have to set up accounts with identical names and passwords in each file. This becomes a major pain if users need to frequently change their passwords. Support for this can be scripted and I think external server authentication can ease this complexity for shared systems also.
- If you move a file, change its name or change the name of an enclosing directory, filemaker may be unable to find the file and you'll get error messages the next time you work with your database.
- A script set to "run with full access privileges" truly runs with full access privileges. In a multi-file system, a script in file1 that attempts to modify data in file2 can't get past the current access privilege limitations on file2 even if the script is set to run with this option.
Advantages to having multiple files.
If you need to update a file or rescue the data from a damaged file, you only have to import the data from the table(s) in that one file instead of all the tables in your database. (You can ease the pain here by writing an update script that imports data into all the tables one table at a time.) You can even split your files into "front end" (interface), "back end" (data) combinations so that script/layout/value list type updates can be deployed simply by replacing the front end file with a new copy.
Our posts are overlapping.
Before I knew better, I told you to replace the tblSow::SourceID fields with tblSource::SourceID fields. Are they still setup this way? Enter layout mode and double click the field. If they're referring to tblSource, switch them back to tblSow.
Thank you Phil for the explanation on that. I didn't realize Filemaker was originally set up to only handle one table. Wow, how limiting that is. For our needs, I don't think separate files is necessary, and I will think how to link this other table like you said, to make some reports. I don't think it is necessary to have them linked, but it might be to our advantage. Thanks again
ok I changed the SourceID field on tblSow layout to lookup tblSow:SourceID, and it wiped out all the SourceID data. It seems if it looks up tblSow:SourceID it wipes out the data and if it looks up tblSource:SourceID it overwrites all the data. Hmmm, I am confused.
Would it be ok if I sent you this file? It's very small, but it might be easier on you to see what I am talking about. Maybe I am missing something obvious here. If you would rather I didn't, I understand too
You can use import records with "new table" selected and then import the data and field definitions into your other file if you want to merge them. Any scripts and layouts have to be brought over separately. That can be a bit of work to avoid breaking things so you may want to keep the files separate just to avoid the trouble.
"ok I changed the SourceID field on tblSow layout to lookup tblSow:SourceID, and it wiped out all the SourceID data. "
Not really but it may look that way. Just removing one field from your layout and placing a different one in its place (that's basically what you did) won't alter the data stored in the two fields. If tblSow::SourceID is blank, then it was blank before you switched to this field. You may have more than one table occurrence in your relationships graph that have the same data source table, tblSow. Make sure in layout setup... that the same name is visible as that shown in the drop down menu in the dialog that pops up when you double click this field while in layout mode.
Just in case the terms "table occurrence" and "data source table" are getting confusing...
Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:
Table vs. Table Occurrence (Tutorial)
Check your private messages (envelope icon in upper right corner). I've sent you an email address you can use for this purpose.