How do I make the creation of a record in one table trigger the creation of a new record in another table?

[Joe_Schmo]

In one layout, I have a formula that auto enters a serial # and assigns it to each new record. The layout contains a portal to another table that I would like to link to this serial #. Is there a way to make the contents of the portal saved as it's own record automatically whenever a new record is created in the main layout?

They would be related by the serial number. I want the portal record to be all automatically filled out and not directly editable by the user. It's just a "record" of what was entered into the main layout.

[Fitch]

You'd need to use a script. You could then assign your script to the New Record command using custom menus.

[Joe_Schmo]

You'd need to use a script. You could then assign your script to the New Record command using custom menus.

If both the table for the layout and for the portal in the layout contain a common field that is related, isn't there a way to use that relationship to create the record in the portal? When I create a record in the main layout/table, that assigns the serial number. There's also a serial number field in the portal's table, and I would like to have it mirror serial number field in the main table. If the two tables share the field then isn't there a way to make the creation of a record in one table cause a new record in the other without a script?

I'm just starting out in FMP, so I'm sure you know more than me- I'm just trying to make sure I'm explaining this correctly so I don't use a script where it's not needed and over complicate things.

[Fitch]

...isn't there a way to make the creation of a record in one table cause a new record in the other without a script?

No.

[Joe_Schmo]

Could you give me an idea of the script or what commands it would use? I'm guessing that I would need to use the "set script trigger" from layout mode on the serial # field right? Then choose "on object enter" and put the script there right?

[Joe_Schmo]

I think I'm close but the script isn't working. Here's what I have so far. The serial # is generated with a calculation for the field [ Safety Clearance::Safety Clearance # ]. I want that same serial number to be used to start a new record and save the # to the field [ SC_IsoPnts::SC# ].

Go to Field [ Safety Clearance::Safety Clearance # ]

Copy [ Safety Clearance::Safety Clearance # ] [ Select ]

Enter Browse Mode

Go to Layout [ “SC_IsoPnts” (SC_IsoPnts) ]

New Record/Request

Go to Field [ SC_IsoPnts::SC# ]

Paste [ SC_IsoPnts::SC# ] [ Select ]

[comment]

Can you explain the purpose behind your quest? In general, you should create a new record only when you have some data for this record to hold.

[LaRetta]

Joe said, "I have a formula that auto enters a serial # and assigns it to each new record. The layout contains a portal to another table that I would like to link to this serial #. Is there a way to make the contents of the portal saved as it's own record automatically whenever a new record is created in the main layout?"

Fitch is correct that it takes a script if you want it to be automatic but Joe, the child doesn't need a record unless there is something to put into it; why create a single totally blank child record? And then you will have blank child records mixed with real child records and YUK, lol. When there is something to put into the child record, the User can either click a button to add a new child record (if they wish to script it) or simply turn on 'allow creation of related' in the graph (on the child side) and begin typing into the portal. It will automatically insert that parent's ID in to the child's ParentID field as well.

Oooopppps! Comment's response wasn't there when I began! Well I'll leave mine since I said it in more concise form. :laugh:

[Joe_Schmo]

Can you explain the purpose behind your quest? In general, you should create a new record only when you have some data for this record to hold.

the portal on the form is a list of isolation points and other related fields from the Isolation Points table. That portal is embedded in a layout that has a serial # auto entered with each new record.

I would like for the portal to be saved as it's own table and have the serial number copied over to it to link the data in the portal to the rest of the data in the layout that's outside of the portal.

Joe said, "I have a formula that auto enters a serial # and assigns it to each new record. The layout contains a portal to another table that I would like to link to this serial #. Is there a way to make the contents of the portal saved as it's own record automatically whenever a new record is created in the main layout?"

Fitch is correct that it takes a script if you want it to be automatic but Joe, the child doesn't need a record unless there is something to put into it; why create a single totally blank child record? And then you will have blank child records mixed with real child records and YUK, lol. When there is something to put into the child record, the User can either click a button to add a new child record (if they wish to script it) or simply turn on 'allow creation of related' in the graph (on the child side) and begin typing into the portal. It will automatically insert that parent's ID in to the child's ParentID field as well.

Oooopppps! Comment's response wasn't there when I began! Well I'll leave mine since I said it in more concise form.

If I could save and access all the info needed without making the portal be saved to a separate table then that would be fine. It's really hard to explain all this without going into immense detail about the project and the purpose of the database. Could I send the file to one of you and maybe meet in the chat room?

[LaRetta]

Joe said, "If I could save and access all the info needed without making the portal be saved to a separate table then that would be fine."

You mean you want a record created in another table besides the Isolation Points table? You might be talking about a join table? As for accessing all the info, you can make a portal row as large as you want or open window or jump to another layout (scripted stuff).

[Joe_Schmo]

The records in the portal are isolation points that already exist in their own table. But I need a record of the specific ones used with this safety clearance #. When I browse a record from the Safety Clearance layout in table view, I only see the first record from the portal. If I want to use the values inside the portal later how do I access them? Where can I view them besides the layout view and how are they references for formulas?

It seems like if the portal was it's own record then accessing them and referencing them would be straight forward like any other table. But the way it is now, I only see the info in the portal when in browse mode with form or list view. When I go to table view there's just that first record from the portal. I'm going to need the info from the portal for another layout later so won't it need it's own table?

[Fitch]

It looks like this thread is taking a different turn, however some questions were asked that I'm going to go ahead and answer just to clear the air:

I'm guessing that I would need to use the "set script trigger" from layout mode on the serial # field right? Then choose "on object enter" and put the script there right?

An auto-entered serial number won't trigger a script, so no, that won't work.

I kind of like the custom menu idea I proposed in my first post, however a script trigger is possible. A script trigger based on creating a new record would need to be a layout trigger, not a field trigger. The OnRecordLoad trigger would do it, but you'd need to test for Get(RecordOpenState)=1 in the script, and only create new related records when that is true.

[Fitch]

Here's your script optimized:

Enter Browse Mode

Set Variable[ $id ; Safety Clearance::Safety Clearance # ]

Go to Layout [ “SC_IsoPnts” (SC_IsoPnts) ]

New Record/Request

Set Field [ SC_IsoPnts::SC# ; $id ]

Go to Layout[ original layout]

Note: another option would be to create related records via a relationship, which is nice in that that there's no layout switching needed, but it may require changes to your relationship graph.

[Joe_Schmo]

Here's your script optimized:

Enter Browse Mode

Set Variable[ $id ; Safety Clearance::Safety Clearance # ]

Go to Layout [ “SC_IsoPnts” (SC_IsoPnts) ]

New Record/Request

Set Field [ SC_IsoPnts::SC# ; $id ]

Go to Layout[ original layout]

Note: another option would be to create related records via a relationship, which is nice in that that there's no layout switching needed, but it may require changes to your relationship graph.

thanks for the script. I'll give it a shot but, I agree that the related record would be better. I tried setting that up as well but I couldn't get the serial # (safety clearance number) from the main layout to populate over into the same field in the second table. Ideally, I would like the contents of the portal to be saved as it's own table/records with a safety clearance # in common with the main table/layout.

[comment]

Ideally, I would like the contents of the portal to be saved as it's own table/records with a safety clearance # in common with the main table/layout.

I think you are missing a very important point here: the "contents of a portal" are records in another table - and only related records will be shown in the portal. IOW, it works the other way round, and nothing needs to be saved.

[Joe_Schmo]

I just uploaded an album of screen shots from the tables/ layouts that I have built. Hopefully this will make it easier to explain what I'm working on.

Comment-

I figured the portal info was saved somewhere, but how would you reference it in a script or relationship? What's the nomenclature? It's no longer just Table::Field. I need to be able to take each record from the portal for a given safety clearance (the main record the portal is in) and use each portal record to print a label and report.

[Fitch]

Create a layout based on the portal records to print your labels. And another for your report.

[Joe_Schmo]

Create a layout based on the portal records to print your labels. And another for your report.

When I use the new layout wizard, it asks which table I want to use fields from. My portal is in the "Safety Clearances" table and the portal itself is not a choice, so I chose the Safety Clearances one. Then I get a list of fields to add to the label, but none of the fields from the portal are there.

[EDIT]

I created a second layout for the labels using the various fields from different tables and it does create it's own record for the first record in the portal but that's it. It's the same as when I view the Safety Clearance layout in table view. All the fields in the main table are there, then there's the info from the first record in the portal. How do I get the rest of the portal records?

[Joe_Schmo]

It looks like this thread is taking a different turn......

Fitch-

You're right. I initially thought that I needed to set up some way to make FMP save the contents of the portal as it's own table record. I see now that it's already being saved when I create a record in the main table that the portal is in. So at this point my question is shifting towards how I can access all the records in that instance of the portal. I'll move it to a new thread so the title more accurately describes the real question. Thanks for your help!