Portal Record Duplication Problem

[Lee J]

Some time ago I posted a problem I face with Duplicating records which are related via a portal.

I still face this problem today and I hope someone can help me.

I have 3 DB's: Quotes, line_items and Inventory

I need to be able to duplicate quotes (not a problem) but also duplicate the related items with the new quote number.

How do I get the DB's to duplicate the previous items, renumber them so that they become available in the new Quote?

Thanks in advance.

[LiveOak]

You will need write scripts in Quotes and Line Items to duplicate records in each file, pass the linking field from Quotes to Line Items using a global and constant relationship, and replace the linking field value in the new Line Item records. -bd

[Lee J]

I have tried using the Replace command in a script but ended up overwriting the previous set of records.

I guess I need to find someway of doing one by one using a Loop?

Haven't had much to do with globals across DB's if you could provide some assistance regards the script format that would be great.

Cheers

[Lee J]

<<LiveOak>>

Finally did it by using a temp exported .TAB file. to remove found set, Export, re-number with new ID, and Imported back.

seems to do the trick.

cheers

[jasonwood]

Is there an easier way?

I'm just not sure how to duplicate multiple records and then change the value of a field in only the duplicates.

[Jim McKee]

Hi Jasonwood ...

If you script the duplication process, create a boolean field and include a script step that sets this field to "1" in the duplicate records. You can then find the duplicated records and enter the new values. The last step would be to return the status of the boolean in the duplicated records to "0" or null.

If you don't script the process, and you have auto-enter record creation date and time fields, you can perform your duplication(s), then find the records created in the time frame of the duplication. When you duplicate a record, the creation time of the original record is not transfered. FileMaker inserts the date/time of the duplication.

Good luck!

[jasonwood]

I guess the trick is that you have to do it through the portal (using the go to portal row, next option) in order to duplicate each related record and set a field.

I was trying to go at it from the related file's point of view, with a found set of records that need to be duplicated... that ain't gonna work.

[Jim McKee]

Jason ...

You can easily duplicate related records in a found set situation rather than stepping through portals. Check out the attached demo.

If you have any questions, email me or post here.

Good luck!

Duplicate_Related.zip

[Ugo DI LUCA]

Whereas it is feasable or not, this keeps intriguing me...

Sometimes, the need for duplicating comes from a "bad" db structure, having therefore to deal with rather complicated solutions.Which was (IMO) the case for the first post of this thread.

If you need to duplicate a quote, this seems to me that this is due to a problem in the Line Item structure, or that you need a "Preferences" File for referencing a Customer's habits in the case he often purchases the same goods (or is given special prices,...).

What exacly is your goal, Jason ?

Why would you duplicate portal records ? Wouldn't it be a new "Main" Record with exactly the same related records ? After downloading Jim's attachment, I'm rather sceptic about the final result given...

[Jim McKee]

Ugo ...

I don't know exactly what Jason is trying to accomplish in duplicating related records. I assume he's trying to automate some kind of frequently recurring process.

My example was silly and simplistic, with no purpose other than showing that related records can be duplicated as a found set rather than stepping through a portal. I'm sure that in Jason's system, the desired result is more complex than simply duplicating and displaying these records.

An example: a school system orders roughly the same group of 40 products every month:

pencils

pens

crayons

chalk

erasers

staples

tissues

light bulbs

reams of paper

toner cartridges

... and 30 other products.

In this situation, you could create a new Invoice each month (obviously with unique InvoiceID), store the new InvoiceID in a global. Then find last month's Invoice, duplicate each related line item record and set the InvoiceID of each duplicate record to the InvoiceID of the current Invoice.

When you display this month's Invoice, the line items are already created. All you have to do is change the Quantity value for each item. In some months you would have to delete some items not ordered, and perhaps add some other line items. But, this is a lot faster than individually creating each line item.

Of course, as you suggested, a customer preferences function could be created which would store a list of the most frequently ordered products, and this could be referenced each month to create the basic set of Invoice line items. You would also have to build a mechanism to periodically update these preferences, because over time customers' preferences change. For example, to lower costs, a customer decides to not purchase new toner cartridges and starts ordering only re-manufactured cartridges. How is this change recorded in the preferences? It seems to me that last month's invoice is the most up-to-date summary of the customer's preferences.

Just an idea.

[Ugo DI LUCA]

Jim,

I had noticed that the demo you provided had no other meaning than demonstrating the feasability, and also agree with the method used.

As Jason is an accurate poster here, I was wondering if his case wasn't a little more complex.

Thanks.

[BobWeaver]

If you haven't already seen Chuck's detailed discussion on this subject, you should have a look at it here.

[Jim McKee]

Bob ...

Thanks for the heads-up on Chuck's article. His use of the "Omit" step is very kewl. It eliminates the need to increment a global counter for the Go To Record step. Excellent!

Another bonus of his article is that I finally understand why FileMaker sometimes places the duplicate record at the end of the found set, and at other times it places it immediately after the record being duplicated. If the found set is Unsorted, it's placed at the end of the found set. If the found set is Sorted, it's placed immediately after the record being duplicated. I've been trying to figure that out for a long time.