Duplicating Sub records into different table

[GregInChrist]

I am looking for the cleanest way to duplicate a given number of sub records from one table records from table into a different table. 

 

As an overview

 

Order table is related to PlateMaterial table and PO table. The PO table is in a different file. The PO table has related records in a table called lineItems.

 

I am being asked to script automatic POs that are populated from the records in PlateMaterial. They should function as a look up and duplicate the data.

 

So from the perspective of Order. User presses a button and all related PlateMaterial records are used to create line items on a new PO.

 

I need to know the best practice for handling this. My RUFF code translation

 

MyScript(){

Create Record in PO Table;

Set PO ID to var;

Open new window with all related records of PlateMaterial

     Loop for each item in found set{

          Set fields to vars;

          Open PO Line Items;

          Create Record;

          Set fields to var data including PO ID for relationship;

          Close PO Window;

     }

}

 

 

I am concerned this will be slow from opening and closing windows in different files. I am also concerned that it will be a pain passing multiple values as a list between the two files.

 

If it were not terrible difficult to do I would almost like to capture all the records data in the PlateMaterial side and save them as a C struct so to say. From my experience Filemaker does a pretty poor job of parsing out lists of variables easily.

 

Hope I made my intention clear. Please let me know if it looks like I am reinventing the wheel, if there is some function that does what I am looking for already.

 

Thanks sincerely for your help!
 

 

 

 

 

 

 

[GregInChrist]

If i understand the following correctly this is what I am looking for. This looks like a solution that is similar to my comment above "If it were not terrible difficult to do I would almost like to capture all the records data in the PlateMaterial side and save them as a C struct so to say." 

 

Can any one confirm that I am on the right track. I think this is what I am looking for. 

 

If I do not hear back from anyone I will test it with display dialogs rather than writing to the new records. I will follow up if this worked for me.

 

 

 

http://www.filemakertoday.com/com/showthread.php/29288-Copy-Data-Between-Tables-in-Script

 

Short answer to your question, version 2: loop through the found set, setting one $variable for each field to itself & Left ("¶", Length ($variable)) & YourTable::ThatField, for each such variable. So that you end up with each $variable containing a stack of values, with a hard return between each value. Then go to the layout of the other table, set a pointer $variable (let's call it $Pos) to 1 and start a new loop, making new blank records, then setting each field in this other table to GetValue ($theAppropriateVariable; $Pos) then incrementing $Pos to $Pos + 1, with an Exit Loop condition: Exit Loop If [GetAsNumber ($Pos) > ValueCount ($OneOfYourStackVariables)

[GregInChrist]

Still need help. I can only pass one variable between files. I knew that was going to come up. Any ideas?

 

Also sorry for having a conversation with myself here! 

[comment]

I am being asked to script automatic POs that are populated from the records in PlateMaterial. They should function as a look up and duplicate the data.

 

Is there a reason for this duplication? Shouldn't relating a PO to the Order (and by extension to PlateMaterial) be sufficient?

 

 

I need to know the best practice for handling this.

 

I believe it would be best to follow this outline:

 

1. start from a layout of Orders;

2. set variables to those fields of the current order that you need for the PO;

3. set variables to those fields of PlateMaterials that you need for the LineItems; (more about this later)

4. go to a layout of POs, create a new record and set its fields from the variables in step 2;

5. set a variable to the current PO_ID;

6. go to a layout of LineItems and create records in a lop, setting each records fields to the variables from steps 3 and 5.

 

Re #3:

If you can be sure that none of the required fields in the PlateMaterials table can be empty, you can set the variables from the Orders layout to =

List ( PlateMaterials::SomeField )

Otherwise you'd need to GTRR from PlateMaterials and loop there.

 

We are also assuming that these fields do NOT contain carriage returns - otherwise you cannot use a return-separated list.

 

 

---

Still need help. I can only pass one variable between files.

 

Your original post didn't say anything about two files. Why don't you combine them into a single file - or at least add the tables of one file to the RG of the other one?

Edited May 9, 2014 by comment

[GregInChrist]

 

 

Is there a reason for this duplication? Shouldn't relating a PO to the Order (and by extension to PlateMaterial) be sufficient?

 

Office people want it to be duplicated. I currently have it related. Short answer is they want it that way. They pay my salary 

 

 

 

3. set variables to those fields of PlateMaterials that you need for the LineItems; (more about this later)

 

Does the script that I posted in my second post above suffice or would you recommend something different?

 

 

 

Your original post didn't say anything about two files. Why don't you combine them into a single file - or at least add the tables of one file to the RG of the other one?

 

 

 

Order table is related to PlateMaterial table and PO table. The PO table is in a different file.

 

I can add a table occurrence. I assume permissions still managed in the other file?

[comment]

Does the script that I posted in my second post above suffice or would you recommend something different?

 

As I said, if you know that the fields are not empty and do not contain carriage returns, you can set the variables directly from the parent record using the List() function. This is significantly simpler than what was proposed in your link (if I understand it correctly, which is not at all certain).

 

 

I can add a table occurrence.

 

And a layout.

[GregInChrist]

As I said, if you know that the fields are not empty and do not contain carriage returns...

 

I can not guarantee either. To maybe better phrase my question... Because I do not know that the fields will be filled or not contain carriage returns, is the solution I found above the simplest(or best) or would there be a better(simpler) way to handle this.

 

From what I gather the solution I found above will work but it might be overly complex? Again I cannot rely on the data being filled in (though it should be) nor can I guarantee the data does not contain carriage returns, that is unless I run each field through some other functions to account for them.

 

Knowing this, what do you suggest?

[comment]

 if you know that the fields are not empty and do not contain carriage returns

 

I can not guarantee either.

 

In such case, I would recommend something like the following. In this example, we will "copy" the values from one field (OrderDate) from Orders and two fields (Size and Color) from PlateMaterials to the new records in POs and LineItems.

Pseudocode, untested. Start from a layout of Orders, with the current order being the one to copy.

 

Set Variable [ $orderDate ; Orders::OrderDate ]
#
Go to Related Record [ Show related only ; from PlateMaterials ]
Set Variable [ $n ; Get (FoundCount) ]
Go to Record [ First ]
Loop
    Set Variable [ $size[Get (RecordNumber)] ; PlateMaterials::Size ]
    Set Variable [ $color[Get (RecordNumber)] ; PlateMaterials::Color ]
    Go to Record [ Next, Exit after last ]
End Loop
#
Go to Layout [ POs ]
New Record
Set Field [ POs::OrderDate ; $orderDate ]
Set Variable [ $poId ; POs::PO_ID ]
#
Go to Layout [ LineItems ]
Loop
    Set Variable [ $i ; $i + 1 ]
    Exit Loop If [ $i > $n ]
    New Record
    Set Field [ LineItems::PO_ID ; $poId  ]
    Set Field [ LineItems::Size ; $size[$i] ]
    Set Field [ LineItems::Color ; $color[$i] ]
End Loop
#
Go to Layout [ POs ]

Note that the $size and $color variables are repeating variables with $n repetitions.

 

 

 

is the solution I found above the simplest(or best) or would there be a better(simpler) way to handle this.

 

Well, that's for you to decide...