Duplicating records between databases

[HazMatt]

I have 4 databases affected:

1) parts.fp5

2) parts_list_items.fp5

3) quotes.fp5

4) quotes_line_items.fp5

I have a layout called "Quote Template" in parts.fp5 where I can list options that a customer would want. Each option has fields like part_number, part_name, part_price, etc. 15 options = 15 related records (basic stuff so far).

I want to be able to click a button, "New Quote", that takes these 15 items and creates 15 new records in the quotes_line_items.fp5 database. I spent the morning putting together a series of scripts and subscripts that accomplishes this, but it relies on the Copy / Paste steps too heavily, and it's slow.

Is there a better way of doing this?

[John Caballero]

How about going to the related parts_list_items to isolate the set you want to duplicate and then importing those records into quotes_line_items and then setting the newly-imported records with the ID from your Quotes file to link it up?

[HazMatt]

Hmm, I've never used the import function within FileMaker... that's a great idea! I'll try it out and post back.

[Ugo DI LUCA]

Hi MDLarson, Hi John,

I just can't see any duplication process here...

The Quotes_LineItems.fp5 bahaves as a 2 levels Line Item, where you'd enter both the PartID and its Optionals, as when dealing with Cars Sales.

You'd just pick the selected optionals from the parts_listitems.fp5, through a portal with relationship PartID::PartID, and insert them in the Line Item as a second level of records, relating to the PartID.

Or I am missing something...

[HazMatt]

Hi Ugo. I can't create a relationship with the parts_list_items.fp5 records directly, because I'm looking for the "master" part number, whereas the best a direct relationship would be able to do is get to the "sub" part number.

We're dealing with assemblies, where parts get assigned to other parts all the time. I hope that makes sense.

John... I should have posted back earlier, but I was so excited to get my project done, I just kept on working! Your suggestion worked great. I was able to simplify my scripts and make it quicker and more reliable. THANKS!

[John Caballero]

Hi MD,

Glad it worked out for you.

Ugo - I hear what you're saying and almost suggested doing what you suggested. This just seemed to fit better what MD was asking to do.

[Ugo DI LUCA]

Hi again,

Glad you fixed it with the Import procedure.

Keep in mind though that it could have been done by simply filtering your items list by the PartID, and using a loop through the related records.

[HazMatt]

Ugo DI LUCA said:Keep in mind though that it could have been done by simply filtering your items list by the PartID, and using a loop through the related records.

Well, yes, I'm aware of that. That's what I did at first, or at least I had a loop in my script that consisted of several subscripts copying and pasting back and forth.

I couldn't figure out how to get the relationship to work in the loop, so I figured there was a more direct way.

[Ugo DI LUCA]

Agreed John,

I had the case recently where I needed the chilren to show in the Line Items with the parent records. In my case, there were variable numbers of chidrens, and even some possible add-ons or substitutions to be made, so I had to pick them from a portal.

Anyway, I tried both methods at first, and was more confident upon the loop than the import.

Just a matter of confidence.

[John Caballero]

Hi MD,

If you'd set your Quote ID into a global, you could've used a constant-to-constant relationship to grab it from the Quote file and use the Set Field script step to set it into the ID field in the related file as your looped through the records. That's probably how I would've done it if I'd set up the files, but it sounded like you already had a certain architecture in place that you wanted to go with.

[Ugo DI LUCA]

About a year ago, another member had a case where he wanted to print a "Load Order", showing all related records (parts) to the Items in his order.

Curiously, I had to do exactly the same job, a few months after.

I changed some parts, but I quite followed the suggestions I had made at this time, which are available in this thread

Print related related

HTH