Looping Script with Record Duplication

[AudreyRae]

I've got a looping script with a subscript that creates a duplicate record when certain criteria are/are not met.

My problem is when the first record in the found set is duplicated, I'm on the last record created (the duplicate), which is *not* the right position to go to the next record.

As you can see below, I've tried omitting records, but that doesn't work either; I wind up skipping the second record when the looping script goes to the "next" record.

What would be the most clever/elegant/appropriate way to loop through the found set of records, duplicating some/all records along the way?

Thank you in advance for your consideration.

Here's the looping script:

REC_17-a Auto-Create INSTALL | PROD for Windows on ALL LI | aja

Go to Layout [ Sales Fields ]

Replace Contents [ Products Services | aja, Replace data:Calculation: , "Products" ]

[ No dialog ]

Go to Record/Request/Page

[ First ]

Perform Script [ "REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | aja" ]

[ Sub-scripts ]

Loop

Go to Record/Request/Page

[ Next, Exit after last ]

Perform Script [ "REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | aja" ]

[ Sub-scripts ]

End Loop

Go to Related Record [ Sales_Sales Order # ]

[ Show only related records ]

Here's the record-duplicating script:

REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | ajaGo to Layout [ All Fields ]

If [ Products Services | aja = "Products" and Window Installation ? fr SQ | aja = "" and Window Installation ? fr SALES | aja = "Y" and

Window Installation Created fr SALES? | aja = "" ]

Copy [ Sales_Sales Order #:Sales Order Number ]

[ Select entire contents ]

Duplicate Record/Request

Paste [ Sales Order Number ]

[ Select entire contents ]

Go to Next Field

Set Field [ Cust Number, Sales_Sales Order #::Cust Number ]

Set Field [ Products Services | aja, "Services" ]

Set Field [ Window Installation ? fr SALES | aja, "" ]

Set Field [ Window Installation Created fr SALES? | aja, "Y" ]

Set Field [ Window Installation ? fr SQ | aja, "" ]

Go to Layout [ Sales Fields ]

Perform Script [ "FLD_31 Set Order Services ID | aja" ]

[ Sub-scripts ]

Perform Script [ "FLD_32 Re-Set Order Products ID to Null | aja" ]

[ Sub-scripts ]

Set Field [ Quote Standing Number MATERIALS | aja, "" ]

Set Field [ Sales Order Products ID | aja, "" ]

Set Field [ Quote Standing Number SERVICES | aja, "" ]

Set Field [ Discount Per Item %, "" ]

Set Field [ Price Override, Window Installation Cost Calc | aja ]

Set Field [ Item Number, Case(

Qty Block Single Calc | aja = 1, "50200414",

Qty Block Single Calc | aja >= 2 and Qty Block Single Calc | aja <= 15, "50200415",

Qty Block Single Calc | aja >= 16 and Qty Block Single Calc | aja <= 35, "50200416",

Qty Block Single Calc | aja >= 36 and Qty Block Single Calc | aja <= 56, "50200417",

Qty Block Single Calc | aja >= 57 and Qty Block Single Calc | aja <= 80, "50200709",

Qty Block Single Calc | aja >= 81, "50200710",

"") ]

Set Field [ Item Name, "Installation > " & Subcategory Display | aja & " - " & Window Size Width | aja & "x" & Window Size Height | aja

& " | " & Window Type | aja & " | " & Style Display | aja & " | " & Color Display | aja & " | " & Block Size AutoEnter | aja & " | " &

Stucco Stop AutoEnterCalc | aja ]

Exit Record/Request

Omit Record

Go to Record/Request/Page

[ First ]

Set Field [ Window Installation Created fr SALES? | aja, "Y" ]

Omit Record

Else

Omit Record

End If

[-Queue-]

A general approach

Unsort

Go to Record/Request/Page [Last]

Loop

Duplicate Record

Omit Multiple [2]

Exit Loop If [not Status(CurrentFoundCount)]

End Loop

Also, you can simplify

Set Field [ Item Number, Case(

Qty Block Single Calc | aja = 1, "50200414",

Qty Block Single Calc | aja >= 2 and Qty Block Single Calc | aja <= 15, "50200415",

Qty Block Single Calc | aja >= 16 and Qty Block Single Calc | aja <= 35, "50200416",

Qty Block Single Calc | aja >= 36 and Qty Block Single Calc | aja <= 56, "50200417",

Qty Block Single Calc | aja >= 57 and Qty Block Single Calc | aja <= 80, "50200709",

Qty Block Single Calc | aja >= 81, "50200710",

"")]

to

If [Qty Block Single Calc | aja]

Set Field [ Item Number, "50200" & Case(

Qty Block Single Calc | aja = 1, "414",

Qty Block Single Calc | aja < 16, "415",

Qty Block Single Calc | aja < 36, "416",

Qty Block Single Calc | aja < 57, "417",

Qty Block Single Calc | aja < 81, "709",

710" )]

End If

assuming, of course, that aja contains only integers.

[Søren Dyhr]

This doesn't make sense:

Copy [ Sales_Sales Order #:Sales Order Number ]

[ Select entire contents ]

Duplicate Record/Request

Paste [ Sales Order Number ]

[ Select entire contents ]

Go to Next Field

What it apparently looks like is that you overwrite an autoenter serial number ...but that will produce gaps in the sequence???

Then is there the problem with tampering with the users clipboard, it's a bad habit!

It can be done this way - investigate the upload!

There isn't much sense in going to a particuar field when using Set Field[ entirely!!!

When it comes to it, what is the point in setting a lot of fields in a duped record??? ...the table structure seems a little flat to be honest!

--sd

slammer.zip

[AudreyRae]

[color:blue]I appreciate your reply, but...

You wrote:

"What it apparently looks like is that you overwrite an autoenter serial number ...but that will produce gaps in the sequence???"

[color:blue]I am duplicating a product line item (LineItem.fp5) record and changing the information in various fields to make it a service line item which *also* needs to have the sales order number (from Sales.fp5) so it will appear on the order. Gaps in the sales order number field (Sales.fp5) sequence are not occuring here in the LineItem.fp5 database.

You wrote:

"Then is there the problem with tampering with the users clipboard, it's a bad habit!"

[color:blue]Thank you for your criticism of "tampering with the user clip board." I agree and it is my preference *not* to use the copy step, but I need it here.

You wrote:

"There isn't much sense in going to a particuar field when using Set Field[ entirely!!!"

[color:blue]I do not understand what you mean here.

You wrote:

"When it comes to it, what is the point in setting a lot of fields in a duped record??? ...the table structure seems a little flat to be honest!"

[color:blue]Ahem... users manually enter a product line item in the Sales.fp5 database (on an order) and also enter "Y" (Yes) on the product line item if there will be in installation/service performed.

Once the order is completed, an error-checking script, which includes many, many subscripts (like the one I'm asking for help with) is run.

The objective is to automatically create service line items for the product line items that have the "Y" value in the "Window Installation ? fr SALES | aja" field.

Everything else works perfectly! The PROBLEM is not being able to loop through the found set of LineItem.fp5 records when duplicates are created along the way; i.e., I'm in the wrong record position (last) to go to the "next" record.

Do you know of a way to get around that when some product line items in the found set are duplicated while others are not?

" ...the table structure seems a little flat to be honest!"

[color:blue]Again, I apreciate your criticism (and your candor), but this solution contains 43 files and does everything from contact/project management and quote/order processing to inventory control and full double-entry accounting. It is quite broad and deep with hundreds of layouts and thousands of scripts. Perhaps if I were able to provide you with considerably more information, your perception might be a little different.

[AudreyRae]

Thank you SO very much for your consideration and your replies. Using your collective advice I was able to make this work... I *love* FileMaker Developers!

Looping to the last LineItem.fp5 record each time worked well, but the line items were duplicated in the reverse order of the original found set, which was okay, but I preferred them to be in the same order as the originals. Going the record number 1 instead of the last record in the looping script fixed that.

Additionally, exiting the script when there were no longer any line item records in the found set, left me with no records and unable to return to the related sales order record in the Sales.fp5 database... a global field fixed that.

Below are the corrected scripts.

Thank you again for your help!

REC_17-a Auto-Create INSTALL | PROD for Windows on ALL LI | aja

Go to Layout [ Sales Fields ]

Replace Contents [ Products Services | aja, Replace data:Calculation: , "Products" ]

[ No dialog ]

Unsort

Go to Record/Request/Page [ "1" ]

[ By Number..., No dialog ]

Perform Script [ "REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | aja" ]

[ Sub-scripts ]

Loop

Unsort

Go to Record/Request/Page [ "1" ]

[ By Number..., No dialog ]

Perform Script [ "REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | aja" ]

[ Sub-scripts ]

Exit Loop If [ Status( CurrentFoundCount) = 0 ]

End Loop

Show All Records

Go to Layout [ Sales Fields ]

Enter Find Mode

Go to Field [ Sales Order Number ]

[ Select/perform ]

Set Field [ Sales Order Number, gSales Order Number | aja ]

Perform Find

[ Replace Found Set ]

Go to Related Record [ Sales_Sales Order # ]

[ Show only related records ]

REC_17-b Auto-Create INSTALL | PROD for Windows on This LI | aja

Go to Layout [ All Fields ]

If [ Products Services | aja = "Products" and Window Installation ? fr SQ | aja = "" and Window Installation ? fr SALES | aja = "Y" and

Window Installation Created fr SALES? | aja = "" ]

Copy [ Sales_Sales Order #:Sales Order Number ]

[ Select entire contents ]

Duplicate Record/Request

Paste [ Sales Order Number ]

[ Select entire contents ]

Go to Next Field

Set Field [ gSales Order Number | aja, Sales Order Number ]

Set Field [ Cust Number, Sales_Sales Order #::Cust Number ]

Set Field [ Products Services | aja, "Services" ]

Set Field [ Window Installation ? fr SALES | aja, "" ]

Set Field [ Window Installation Created fr SALES? | aja, "Y" ]

Set Field [ Window Installation ? fr SQ | aja, "" ]

Go to Layout [ Sales Fields ]

Perform Script [ "FLD_31 Set Order Services ID | aja" ]

[ Sub-scripts ]

Perform Script [ "FLD_32 Re-Set Order Products ID to Null | aja" ]

[ Sub-scripts ]

Set Field [ Quote Standing Number MATERIALS | aja, "" ]

Set Field [ Sales Order Products ID | aja, "" ]

Set Field [ Quote Standing Number SERVICES | aja, "" ]

Set Field [ Discount Per Item %, "" ]

Set Field [ Price Override, Window Installation Cost Calc | aja ]

Set Field [ Item Number, Case(

Qty Block Single Calc | aja = 1, "50200414",

Qty Block Single Calc | aja >= 2 and Qty Block Single Calc | aja <= 15, "50200415",

Qty Block Single Calc | aja >= 16 and Qty Block Single Calc | aja <= 35, "50200416",

Qty Block Single Calc | aja >= 36 and Qty Block Single Calc | aja <= 56, "50200417",

Qty Block Single Calc | aja >= 57 and Qty Block Single Calc | aja <= 80, "50200709",

Qty Block Single Calc | aja >= 81, "50200710",

"") ]

Set Field [ Item Name, "Installation > " & Subcategory Display | aja & " - " & Window Size Width | aja & "x" & Window Size Height | aja

& " | " & Window Type | aja & " | " & Style Display | aja & " | " & Color Display | aja & " | " & Block Size AutoEnter | aja & " | " &

Stucco Stop AutoEnterCalc | aja ]

Exit Record/Request

Omit Record

Go to Record/Request/Page

[ First ]

Set Field [ Window Installation Created fr SALES? | aja, "Y" ]

Copy [ Sales Order Number ]

[ Select entire contents ]

Omit Record

Else

Omit Record

End If

[Søren Dyhr]

I am duplicating a product line item (LineItem.fp5) record and changing the information in various fields to make it a service line item which *also* needs to have the sales order number (from Sales.fp5) so it will appear on the order.

Ah it's a foreingkey! That explains a lot - but there is then a structural problem here. Duping a record here is in my opinion wrong (Boyce/Codd are probably with me on this one) ...when it comes to it is a service-line item and sales-line item two representations of the same record with a few changes ...so all it really takes from the script must a switch to another layout ...the structural dependencies is down to calcfields an the user entries in the new layout.

Alright I didn't see that your solution was for 6.0 so the upload is out of reach - but as it is with filemaker solutions are there at least three solutions to a problem - here like a title of a Spaghetti Western "The Good, The Bad and The Ugly" .

The Good is the thoroughly considered solution, the Bad is the solutions based on lack of knowledge and finally the Ugly where only prejudice are supposed to have a meaning. Nobody should ever be forced to tamper with the clipboard ...well you can of course but it's quite inconsiderate if you don't take precautions.

I would say the data-structure is inadequate and the sole reason for the scripting in the first place, but you could at least put the number in a global field before duping via a Set Field[

Do you know of a way to get around that when some product line items in the found set are duplicated while others are not?

I do, take a look at this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=587&mode=thread&order=0&thold=0

...and try to guess how it's done - He's giving hints!!!

If you can allow me time until to monday will I try to make a pre 7.0'ish attempt on how different I think the task of duping should be ...the only required looping is the one Matt Petrowski uses, the rest is up to structural dependencies with calcfields and perhaps a few lookups (yikes)

--sd

[AudreyRae]

If you can allow me time until to monday will I try to make a pre 7.0'ish attempt on how different I think the task of duping should be ...the only required looping is the one Matt Petrowski uses, the rest is up to structural dependencies with calcfields and perhaps a few lookups (yikes)

Thank you for your additional thought and consideration, but the scripts work perfectly now (refer to my previous post). Please do not trouble yourself any further.