If I create a record within the _Sub_Table (and I have the option checked in the relationship to allow a record creation in the main table via this relationship) should it not create a record in the main table and put the _kf_MainID into the Sub_Table field of _kf_MAINID?
Correct. That feature only allows you to add records inside of portals. That will never automatically create a related record though.
What you need to do is loop through your child table records after import and create the related parent records via a script.
That's kind of what I was backing into, it seems a bit drab but I guess it is what it is. I was hoping to take advance so automated functions in the process. But since I am importing the file I miss some opportunities to add things, I guess I will have to script it.
Thanks for the pull back into reality.
If it was (in reality) a one-to-one relationship, you could *technically* export from the child table and reimport that to the parent table.
Unfortunately, until ExecuteSQL() gets opened up to allow for more than just SELECT, there's really no great way of automatically creating related records.
I think that rather than do a loop cycle, I will save the ID to a variable and then embed in into an autocomplete filled as the record is being created.
This shoud save a little effort I would think.
It seems like an over sight on Filmaker part.
I wouldn't place the blame on filemaker. There's no RDBMS that makes it THAT easy to create related records without writing some code out (IE SQL you can do it, but it's not beginner level INSERT statements.)
The setting distinctly states "Allow Creation" not "Automatically Create". This single setting DOES make portals wildly useful. Otherwise you'd have to always script to create a record in a related table, setting the parent ID to variable, going to a different layout, creating a record, returning, etc.. But I can just tell users to start typing in a blank row in a portal in filemaker.
Mike & Jason --
There's also something that bothers me about the description Jason gave and how I see creating related records. If I understood Jason's request, he wants the child record to be able to create the parent (and possibly the grandparent). He shows a key relationship similar to
parentID_in Child <--> parentID (in parent record)
and then asked if, when creating the child record, wouldn't the parent record both be created and the parent's ID copied into the child record.
Even if you had tried to do this manually with a portal it would not work. The 'parentID_in Child' is going to be blank initially, and thus there is no value to put into the parent record upon auto-creation. (Auto-creation is a what I'd call a 'push' process, not a 'pull' process.) While I suppose FileMaker is likely to try and work with you here, either the auto-entered parent ID is going to be filled in and the child record (with the empty value) is not going to be linked to the correct parent record, or the parent ID will be blank (and thus linked), but so will every other record thus created, and you still have no proper links.
In this situation, I'd go with a separate utility file (and maybe not just a table) containing the basic child records using the straightforward FileMaker import, that uses a loop to:
1) Create a grandparent record
2) Bring that grandparent ID back into the import utility file
3) Create a parent record (and use the grandparent ID when creating it)
4) Bring the parent ID back into the import utility file
5) Create the child record (using the appropriate parent and grandparent IDs)
While looping through the child records figure out a way to know when it is required to create new parent and grandparent record.
While this is slower than the straight FileMaker import, you get all the control you need, and this will play nice in a multi-user situation as a bonus. I've done this as far back as FileMaker 5/6, and it gets the job done.
And for gosh sake, use UUIDs for internal linking and such. Once you get this right, you'll want to be able to keep everything linked up without relying on some "ID" that was in Excel....
-- Drew Tenenholz
His graphic above only showed a one-to-many relationship between two tables, which is why I suggested a loop after import to create the MAIN_TABLE records after importing SUB_TABLE
That loop would look something like this, run from the SUB table after import:
Go To Record (first)
Go To Layout (MAIN_TABLE)
Set Variable $id = MAIN_TABLE::idKey (auto enter serial)
Go To Layout (original)
Set Field (SUB_TABLE::MAIN_TABLE_idKey) = $id
Exit Loop if ( get (foundCount) = get (recordNumber) )
Go To Record (Next)
This would only really work for one-to-one relationships without more robust code. Drew's method is a lot more robust though to account for more variations of the data, as well as the three tiers.
Mike / Drew
Rather than do the loop routine, I thought to create the parent record a head of time (the import) and set a variable with the Parent ID, then in the Linking ID (child record) I set up a Autoenter Value that is previously saved variable. Then it just fills in the Parent ID during the import process, it seems to work ok.
What do you think about this method? The only time a Parent Record will be created is during the import cycle so I do not need to worry about if one is created outside of the import.
The only time a Parent Record will be created is during the import cycle so I do not need to worry about if one is created outside of the import.
That's a luxury most developers don't have to work with, and makes your method acceptable in my eyes.
I would caution to lock out user privileges to the parent table to block creation,,, just to be safe.
Ok point of all the imported records is to use them within a chart as I have multiple charts per job. Thus the Grandparent (JOB), Parent (CHART ID), and Child (CHART LINEITEMS), which I think is the correct set up in this instance.
The results of the chart seem to be using all the record for the Grandparent record and not by the Parent records. See below..
Within that chart I have it being filtered by a unique identifier, The blank spaces tells me it is using the Grandparent ID as a base and not the Parent.
Thanks again for your thoughts,
"Even if you had tried to do this manually with a portal it would not work. The 'parentID_in Child' is going to be blank initially, and thus there is no value to put into the parent record upon auto-creation. (Auto-creation is a what I'd call a 'push' process, not a 'pull' process.) While I suppose FileMaker is likely to try and work with you here, either the auto-entered parent ID is going to be filled in and the child record (with the empty value) is not going to be linked to the correct parent record, or the parent ID will be blank (and thus linked), but so will every other record thus created, and you still have no proper links."
This is inaccurate. FileMaker can handle this situation just fine. _fMainID will populate with the key of the newly created record in the Main Table.