duplicate set of records

[Learn*2]

Hi, I need to know how I can duplicate a set of records in a single layout.

How can I make a script that automatically duplicates the set ( a batch) and how can I identify the set I want to duplicate before I duplicate it.

[dj]

Originally posted by Learn*2:

[qb]Hi, I need to know how I can duplicate a set of records in a single layout.

How can I make a script that automatically duplicates the set ( a batch) and how can I identify the set I want to duplicate before I duplicate it. [/qb]

If there were a lot of records to duplicate I would use Export Import sequence.

What do you mean with "identify the set"?

 

Dj

[Learn*2]

Thank you for taking interest in my problem. I will try to amplify the information since I´m "stuck" and I´m shure that someone with experience and knowledge will resolve this.

I have a database, and one of the layouts is for invoicing in a club. The problem I have is that I should be able to duplicate (in that layout) the invoices that where issued the month before, so as to avoid to have to manually make one by one for the next month. For example in the month of July I should (with an adecuate script) duplicate the invoices that were issued in June without losing the infromation of previous months. If I duplicate the whole set (of the previous month) I could avoid manually making 1500 invoices for the next month. The ideal thing would be to select the set by the month I want to duplicate and generate that new set of invoices in the same layout.

Thank you once again for your interest and would be very grateful if you could help me build a script to perform what I need.

[andygaunt]

Hi,

 

If you want to duplicate a set by a date range you first need a way of entering that in the find criteria.

 

this can be done manually by entering find mode and in your date field type xx/xx/xxxx...xx/xx/xxxx for the start and end dates you wish to find.

 

Or, it can be done via scripting as well. This could take on the form of two global date fields that once entered, you hit a button that performs the find for you.

 

Or, it can be done by entering a month value, but this can cause problems if you run year in year with the database.

 

Now, as to what DJ was saying, and I must concur with my learned colleague. You will want to first export the records and then import them back in.

 

Now, to do this, you first do a manual export of the records to a file. Then, in scriptmaker you write a script that has

 

Export records [restore export order, perform without dialog]. If you want to export to the same file over and over, set the specify file option as well.

 

OK, now you do a manual import from this exported file.

 

Once this is done, again you write a script that uses

 

Import Records [restore import order, perform without dialog]. If you want to import from the same file over and over, set the specify file option as well.

 

OK, now, you can then write a script that combines these two scripts into one using the perform script control.

 

Hope this helps you get started on the road.

[Russ Baker]

You can duplicate a set of records within a file without an export/import routine. Assuming you have a script which finds the date range of records you want, first create a text field called "Part of Most Recent Duplication". The script is:

 

Show All Records

Replace [NoDialog, "Part of Most Recent Duplication",""]

Perform Script[Your script to find the records you want to duplicate]

Unsort

Got To Record/Request/Page [First]

Loop

. Exit Loop If [status(CurrentFoundCount) = 0]

. Duplicate Record/Request

. SetField ["Part of Most Recent Duplication","Yes"]

. Omit Record

. Got To Record/Request/Page [First]

. Omit Record

End Loop

 

The script works because in a Unsorted state, Filemaker places duplicate records at the end of the current set of records.

 

At the end of this script you will be left with a zero found set of records. At this point, you can either choose to:

 

a. Show All Records, or

 

b. Show the duplicate set you have just created by adding these script steps (thats why you needed to create the "Part of Most Recent Duplication" field).

 

Enter Find Mode[]

SetField ["Part of Most Recent Duplication","Yes"]

Perform Find[]

[Learn*2]

I tried what Russ baker suggested and I believe that I have advanced some in resloving the problem, but I think that I might have fowled up something along the way.

1st: The script that is supposed to find the date range works fine on it´s own, but not when it´s incorporated into the main script.

I made the script like this:

FIND BY DATE

- Show Message(Please, introduce the complete range of date of the month you want to duplicate, then press "Enter")

- Enter Browse[]

- Go To Layout["Invoice"]

- Go to field["date"]

- Enter Find Mode[]

 

2nd: When I run the main script without the previous script what happens is:

1)A message window comes up with "This script cannot be found or has been deleted", then when I press "OK" another message window comes up saying " The previous scriptstep Perform script, could not be completed becase of an error. Do you wish to continue with this script? I then press "Continue" and it gets into the loop, but it doesn´t come out until I press "esc". Then I find that the script has duplicated the records and eventhough at hte end of the script I placed "Show All Records, it shows "Found:0".

 

3rd:The problem with the duplicated records, once I press CTRL+J, is that the following infromation isn´t duplicated on each invoice:

item N°, name of item, description, qty, price and total.

What does show up is : Member N°, name, surname, address, and personal I.D.

 

How do you think I can fine tune the script?

Is it necessary to place the replace field in the layout in the browse mode?

 

Once again thanks for your help and suggestions.

[Russ Baker]

OK,

 

The "Part of Most Recent Duplication" field should be on the layout where you are doing the replace.

 

I suspect the problem is being caused by your Find Script not actually executing the find when you run it as a sub-script. This is because, when you look at it, the last thing it does as a script is to enter the Find Mode. When you run this by itself, it leaves you at a point where you can hit the ENTER key to execute the Find - but in a subscript, it will just be hanging out there... and send you into the next part of the script in the Find Mode.

 

Your Find Script should read:

 

Set Error Capture [On]

Show Message["Please, introduce the complete range of date of the month you want to duplicate, then press "Enter""]

Go To Layout["Invoice"]

Go to field["date"]

Enter Find Mode[Pause]

Perform Find[]

if[status(CurrentError) = 401]

. Show Message ["There are no records in that date range"]

. Go To Layout [Original Layout]

. Show All Records

. Exit Script

End If

 

If your search is going to be a date range, then it would be better to create 2 global date fields, g_Date_From and g_Date_To for the user to enter the 2 dates into. Then use this script step to get the range into the find.

 

Insert Calculated Result [select,"date",DateToText(g_Date_From&"..."&DateToText(g_Date_To"]

 

In relation to the fields that don't show, see if fixing up the Find will correct this, otherwise... Are these related fields or Lookups? If so you may have to re-lookup as part of the script.

[Russ Baker]

OK, here is a longer script which includes the Find for either as single invoice date or a range of dates. I've heavily commented the script to explain the separate sections - and also emailed you a demo file. This script starts on a layout which includes the fields:

 

. g_Date_From (global date)

. g_Date_To (global date)

. Part of the Most Recent Duplication (text)

 

Set Error Capture [ On ]

# Check to see if there is a search date entered in the Start Date field.

If [ IsEmpty(g_Date_From) ]

. Show Message [ Buttons: “OK”, “”, “”; Data: “You must either enter a Start and Finish date for the search, or if you want to use a single date, enter it in the Start Date field.” ]

. Exit Script

End If

# Reset all records as not being part of the new duplicate set.

Show All Records

Replace [ Part of the Most Recent Duplication, Replace data: Calculation: , "" ][ No dialog ]

# Find the records to be duplicated

Enter Find Mode []

# Determine if it is a single date or a date range to be searched for.

If [ IsEmpty(g_Date_To) ]

. # Set for a single date to search for.

. Set Field [ Invoice Date, g_Date_From ]

Else

. # Set for a range of dates to search for.

. Insert Calculated Result [ Invoice Date, DateToText(g_Date_From) & "..." & DateToText(g_Date_To) ][ Select entire contents ]

End If

# Execute the search.

Perform Find []

If [ Status(CurrentError) = 401 ]

. # If no records are found, give the user a choice to go back to the list or re-enter search dates.

. Show Message [ Buttons: “Back to List”, “New Dates”, “”; Data: “There are no invoices which match your date search criteria” ]

. If [ Status(CurrentMessageChoice) = 2 ]

.. Go to Field [ g_Date_From ][ Select/perform ]

.. Exit Script

. Else

.. Show All Records

.. Go to Layout [ Invoice ]

.. Perform Script [ “Sort by Invoice Date 1-9” ] [ Sub-scripts ]

.. Exit Script

. End If

End If

# Unsort the records and loop through the duplication process.

Unsort

Go to Record/Request/Page [ First ]

Loop

. Exit Loop If [ Status(CurrentFoundCount) = 0 ]

. Duplicate Record/Request

. Set Field [ Part of the Most Recent Duplication, "Yes" ]

. Omit Record

. Go to Record/Request/Page [ First ]

. Omit Record

End Loop

# Find only the duplicated records which have just been created.

Enter Find Mode []

Set Field [ Part of the Most Recent Duplication, "Yes" ]

Perform Find []

# Clear the global date search fields.

Set Field [ g_Date_From, TextToDate("") ]

Set Field [ g_Date_To, TextToDate("") ]

# Sort and view the duplicate set of records just created.

Go to Layout [ Invoice ]

Perform Script [ “Sort by Invoice Date 1-9” ] [ Sub-scripts ]

[Learn*2]

Mr. Baker

 

The script is as precise as a Swiss watch!!!!!

As you suggested I changed the Find script and then added the last part:

 

The sciptEnter Find Mode[]

SetField ["Part of Most Recent Duplication","Yes"]

Perform Find[]

 

and it performed as planned.

 

As to the fields that don't show, they still don’t show up. Let me describe what these fields are and what they do:

These fields come originally from other files:

 

Field File

Prod. N° Products

Name Product Products

Product Description Products

Product Price Products

Terms of payment Sales History

Qty manual Sales History

Total line price Sales History

 

When the "Prod.N°" is placed in the field it activates "Name Product" and "Product Description"

 

When the "Terms of Payment" is placed it activates "Product Price".

 

In the invoice, these fields are placed in a Portal and accesed through a relationship:

INVOICE FILE and SALES HISTORY

The matching field in INVOICE is “fact Rel” (number, indexed, autoserial, can´t modify auto)

The matching field in SALES HISTORY is “factura N°” (text, indexed, calculated value = Invoice factura::fact Rel , not evaluated if reference fields are empty.

 

Since all of this information is “kept” in the “Sales history” file, isn´t there a way of copying it and pasting it in the new records.

You mentioned Re-lookup, if that can be used how do I deploy it?

Thanks, once again.

PD: Since I just logged in I just saw the other Find script. Let me check it out and then I´ll comment on it.

[Russ Baker]

If the fields you describe are Lookups, you can update the LookUp by adding these 2 lines to the end of the script:

 

Relookup [No Dialog, "Product No"]

Relookup [No Dialog, "Terms of Payment"]

 

but you shouldn't have to do this so I suspect the problem lies in a relationship with your invoice number based on "Fact rel". Because this is an auto enter index number, the numbers entered in the duplicate records will be different to those in the original, so the relationship will not "see" anything that matches. You may have to think about a differnet system of creating these serial numbers, or performing an external script in the child file which duplicates all the associated records, allocating them the appropriate new serial numbers if you want exact duplicates.

 

BUT - if the purpose is to get a new sew of records based on last months, then wouldn't you want the associated items empty anyhow? - so you could populate them with the next month's purchases etc.

[Learn*2]

Ok, I´ll try the lookups.

 

The basic idea is to fully duplicate the contents of the invoices since they practically don´t change. What does have to change is the control number (Fact Rel) and the invoice number which have to be unique, since those numbers are linked later on to two other files: accounts recievables and taxes.

 

...................

Learn*2

Caracas, Venezuela