Creating a related record

[xochi]

Anyone have an opinion on the relative merits of creating a child record in a related table via various methods? I'm thinking of the circumstance where you have a portal on the layout, but you need to have the related record creating scripted.

Method 1 : Create the new record by (A) allowing creation of related records via. the relationship, ( going to the last portal row, and © setting any related field value, which will automatically create the related record with the proper foreign key.

Method 2: Create a dedicated script to create a new record in the child table. Pass the foreign key as the script parameter. Go to layout (child table). New record. Set field (foreign key=thekey). Go To layout (original layout).

I see pros & cons to both.

Method 1 requires you to alter your TO design, requires you to have a portal on the layout, and alters the look of the portal (showing the extra blank record).

Method 2 can be used w/o a portal, and can be called from pretty much any layout in your solution. However, it has a "Goto layout " step which can be slow, and can cause your tab panel to switch tabs if you aren't on the default tab.

Other ways of doing it?

[comment]

Method 3

[xochi]

Am I correct in thinking that Method 3 goes something like this?

(A) Create a 2nd relationship between the tables, based on a different set of keys (call them the "alternate foreign key")

( Generate new unique alternate foreign key which is guaranteed to be unique.

© Use Set Field() in the related table to set the real foreign key field to this value. This will create a new related record, because the alternate foreign key is unique.

If I'm understanding this, I think I may prefer method 2 still, as it seems that Method 3 requires you to add 1 new field, and 1 new relationship / set of TOs to the TOG. Method 2 is the "cleanest" in the sense that it doesn't impact your relational design.

[comment]

More or less correct, except (, since only one alternate field is required. Since I have posted the demo, I have come to consider Get (AccountName) as the best candidate for this, as it allows multiple users to do this simultaneously.

The preferred method, again, depends on the circumstances. If you have a lot of parent fields to transfer to the child, this is quick and simple.

[Inky Phil]

I run the risk of looking really foolish (no change there then!) if I have got this totally wrong but if I haven't it is an important caveat.

In my multi user system I established a temporary relationship by setting both sides to status(currentusername)(FM5.5). I thought this would allow users to simultaneously input orders ,print out the paperwork and pass the data on further.

At certain times data was getting stored to the wrong record after printing the paperwork . It was only ever noticed at a much later stage so it is difficult to know why an otherwise reliable script should perform badly on the odd occasion.

The only thing I could think of was that the only two people who might put orders in at the same time shared a printer via one of their workstations and that this was causing the current username to get confused at some point.

Stop them putting orders in at the same time - no problems!

I am rewriting my solution because of this and other issues and I am still not sure how to overcome it.

Would using Get(accountname) work better?

Regards

Phil

[comment]

Get (AccountName) should work better in the sense that it must be unique. However, I didn't fully understand your description, specifically the "setting both sides to status(currentusername)" part.

The match field on the child side must be indexable. You can address the ParentID field (i.e. the foreign key), or any other indexable field (as long as it's a text field - if you're using account name).

The important bit is to break the relationship as the last step of creating the record, e.g.

Set Field [CreateChildren::Field1 ; "data1" ]

Set Field [CreateChildren::Field2 ; "data2" ]

...

Set Field [CreateChildren::ParentID ; Parent::ParentID ]

The first Set Field[] created the record.

The last one changed the foreign key, so the new record is no longer related by the CreateChildren relationship. Now there should be NO related records thru this relationship, so running the script again will create another new record. IOW, the relationship acts as a "virtual last portal row".

[xochi]

Get (AccountName) should work better in the sense that it must be unique.

This might fail in the case where someone is logged on twice on different machines. Maybe a combination of Get(AccountName) & Get(CurrentHostTimeSamp) or IP would be safer?

Probably not a big deal tho.

[comment]

That's an interesting point - I didn't realize you could do that. Although for failure to occur, the user and his alter ego would have to run the script simultaneously. Filemaker obviously knows how to tell these two apart - for example, each has their own global values - but this information is not available to the developer.

I wonder about all the solutions that use a user table to store preferences...

[Inky Phil]

Comment, just as you couldn't understand my method, I couldn't follow yours.

I didn't doubt for one moment that yours was the correct one so I've been looking at your example for ages now and puzzling how it worked.

The penny has just dropped.

How simple is that !

Thanks

Phil

ps Now I know why you couldn't follow my method