Copying related records from one table to another

[picnichouse]

Hi,

My database has three tables. You can see the relationship here:

The main table is called "Load" and it is related to the table "CustomerCommission" by the field "CommissionCustomerID" and is related to the table "LoadCommission" by the field "LoadID".

I want to find all the records in the "CustomerCommission" table that match the current Load's "CustomerCommissionID" and copy some of their fields to new records in "LoadCommission" table (there is a portal to the "LoadCommission" table in the Load layout.

Here's the script:

Go to Layout [ "Load" (Load) ]

Go to Related Record [ From table: "CustomerCommission"; Using layout: "CustomerCommission" (CustomerCommission) ]

[ Show only related records ]

Loop

Set Field [ LoadCommission::LoadID; Load::LoadID ]

Set Field [ LoadCommission::Type; CustomerCommission::Type ]

Set Field [ LoadCommission::Amount; CustomerCommission::Amount ]

Set Field [ LoadCommission::AccountName; CustomerCommission::AccountName ]

Commit Records/Requests

[ Skip data entry validation; No dialog ]

Go to Record/Request/Page

[ Next ]

End Loop

Go to Layout [ "Load" (Load) ]

I know I'm missing some steps... never done a copy like this before. When I run it, it just keeps overwriting the first record in "LoadCommission", it never actually adds another record.

Thanks folks!

Andrew

Edited August 28, 2005 by Guest

[comment]

I am guessing the first time around a record is created in LoadCommission.

The second time, there already is a related record, so the Set Field steps operate on the existing record, setting its fields over and over again.

The solution is to use a dummy relationship to create and set the related records, then as the final step set the match field to the proper value.

[picnichouse]

I'm not sure what you mean. What is a dummy relationship, could you give me an example of how it applies?

I'm glad to know that this can be done, but have no idea what you mean at all.

Thanks!

[comment]

I have posted a demo of the concept in this thread.

[picnichouse]

Thanks comment,

I kind of understand.. but not really. Would you mind taking a look at my file and see if you can tell me what I need to change?

test.fp7.zip

Edited August 28, 2005 by Guest

[LaRetta]

Can you post it for us? If you've tried, it didn't stick. :wink2:

[picnichouse]

whoops!

it's on there now.

[comment]

Can you explain in a few words what this is about? Your table names don't mean anything to me, so it's hard to understand what you're trying to do here - in general, and why data is to be copied in this case.

[picnichouse]

Sure.

Commissions in our sales database are set depending on the customer. For example, a job for customer A might give commissions to three different people. That is set in the CustomerCommissions database.

When someone creates a new Load (like a new order), these customer commissions need to be copied over to the LoadCommissions table so we have the ability to override the customer commissions on a per order basis without affecting the basic commission rate that we usually offer for that customer.

This script will automatically run when someone creates a new record in the Load database. It searches for all the records matching the new Load's customerID in the CustomerCommissions database and copies over the relevant fields to new records in the LoadCommission database.

Hope that makes sense...

[comment]

I hope I understand this correctly.

Well, you COULD do it this way (see attached), but it seems simpler to find the relevant customer commisions (by GTRR), then import them into load commisions (with LoadID auto-entered from a global field).

Forgive me for changing your names - I get distracted otherwise.

test.fp7.zip

[picnichouse]

Thanks a million comment, I think we're almost there...

I can't figure out what the difference is between your database and mine that yours works. In mine, it continuosly overwrites the first OrderCommission record in the portal; it never adds more than one. I made the changes to my file trying to mimic your techniques... can you please tell me what I'm still missing? By the way, do you need to have the "Customer" table in there? Seemed like I could have done without it... and why did you match cCreator in "Customers" to "OrderID"? That one blows my mind.

Thanks so much

orders.fp7.zip

Edited August 29, 2005 by Guest

[comment]

cCreator is the trick here. It creates records thru a "dummy relationship", sets them to the values of the current parent record, and "releases" them by setting OrderID to what it really should be. That way, the dummy relationship always has only one, new, record in OrderCommissions.

(Since cCreator = AccountName, in a multi-user scenario each user has their own "scratch record", and no conflict arises.)

You don't NEED the Customer table, but I had to see some order in front of me to understand what I was doing. Seems to me that where there are customer commisions, there must be customers - don't you think?

[picnichouse]

very clever!!

So you can't really scroll through portal rows, adding new ones, in a normal way, eh?

It takes a bit of thinking to get your head around this method, but it works. Thanks, you saved my life!

[comment]

you can't really scroll through portal rows, adding new ones, in a normal way, eh?

Yes, you can - only not thru THIS relationship (the dummy one). You'd be creating orphaned records. You can still add records in the "normal" relationship portal (if you have enabled creation in the relationship's definition).

[IdealData]

Go to Layout [ "Load" (Load) ]

Go to Related Record [ From table: "CustomerCommission"; Using layout: "CustomerCommission" (CustomerCommission) ]

[ Show only related records ]

Loop

Set Field [ LoadCommission::LoadID; Load::LoadID ]

Set Field [ LoadCommission::Type; CustomerCommission::Type ]

Set Field [ LoadCommission::Amount; CustomerCommission::Amount ]

Set Field [ LoadCommission::AccountName; CustomerCommission::AccountName ]

Commit Records/Requests

[ Skip data entry validation; No dialog ]

Go to Record/Request/Page

[ Next ]

End Loop

Go to Layout [ "Load" (Load) ]

You're not getting any new records because you're not forcing FMP to go to the last record in the portal. This is the only place to add new data in a portal. Insert it immediately after the loop step.

If you're going into scripting then you might want to get FM7 Developer for the script debugger.