Creating a record based on a relationship

[OffTheDeepEnd.com]

Here is what I need to accomplish... It seems like an easy script but perhaps my brain is just not functioning this morning...

Two Tables, One called "Order Import Data" and "Shipping Info"

Each record in "Order Import Data" has 5 fields of shipping information that needs to be extracted to the "Shipping Info" table. So the script would need to create the record and then using an order number relationship place the corresponding information into the shipping info table. It seems pretty straight forward but for the life of me, I cannot get it to work correctly. Any ideas would be a huge help.

Thanks!

Edited March 6, 2007 by Guest

[aldipalo]

Ooops, don't know if this will work with FM 6 or 7.

Set variable($OrderID;OrderImportData::OrderID)

Go to Layout(Shipping Info)

New Record Request

Set Field(ShippingInfo::fk_OrderID)

Blah

Blah

Blah

That should do it. If not please post your script.

HTH

Al

Edited March 6, 2007 by Guest

[T-Square]

Al--

Your script won't work in 6. But setting the fields in the Shipping Info table to auto-enter based on the related Order Import Data record should.

David

[LaRetta]

But David, that won't create a related record. Script must set a field in the related table. What am I not understanding?

If Allow Creation of Related is checked (in the relationship graph, on the Shipping table), then you can import and you will end up with your found set of imported records. Then loop through them, setting a shipping field (any field but the ID; such as a date (?)) and it will create your related records and automatically insert the newly acquired parent IDs in each Shipping record as it goes.

Since imports land you on the first record you don't need to preceed with Go To Record/Request/Page [ First]. Script can be simple:

Loop

Set Field [ Shipping::ShipDate ; (whatever) ]

Go to Record/Request/Page [ Exit after Last ; Next ]

End Loop

This would work regardless of the FM version. You only need to set ONE related field; the rest can be auto-enter as David suggests.

LaRetta

[aldipalo]

I don't know, maybe I'm missing something. (See my saying below )

I read that he wants a script that will take him from the Order layout to the shipping layout and create a new record that includes the 5 fields from the order table. In that case shouldn't the script I used work?

BTW, I did notice an error in it.

Set variable($OrderID;OrderImportData::OrderID)

Go to Layout(Shipping Info)

New Record Request

Set Field(ShippingInfo::fk_OrderID;[color:red]$OrderID)

Blah

Blah

Blah

Why wouldn't this script, with changes to apply, work? I use similar scripts in my applications now.

[LaRetta]

Sure it'll work. But why bother jumping back and forth through each import order - into the other table at all when the relationship is one-to-one and can be handled right within the import table (where other work may also take place). Once the shipping record has been created (by whatever means), setting the additional fields can be done within import table directly to the shipping record or via auto-enter on the shipping fields themselves.

Since this sounded like an on-going process, and since it is 1:1 to shipping, Allow Creation works a treat here.

[T-Square]

LaRetta, Al--

You're right, I failed to read closely enough. My option would set the values when a record got created, but failed to initiate the new record.

Al--your scripts won't work in 6 or 7 because you keep using variables. They didn't show up until 8.

LaRetta, I hadn't interpreted that the original poster was performing a batch process, but was looking for ongoing data input workflow. You may be correct though that they were looking for a batch process.

Regardless, I see now that the original poster has "upgraded" to 8, so Al's script will probably work. I would simplify it, as LaRetta mentions, by combining Al's approach with mine. Personally, I would use a Script Parameter set to the current record's ID, though:

If[Get(Scriptparameter) <> "" /* Test for ID first */]

Go To Layout["Shipping Info"]

New Record Request

Set Field[shippingInfo::fk_OrderID; Get(ScriptParameter)]

#All the other fields get set by the autoenter

Commit Record/Request

End If