Jump to content

Have to use Copy/Paste instead of Import - Is there another method


Recommended Posts

(Background) 10 Filemaker Pro 6 (.fp5) files converted to one single Filemaker Pro 14 (.fmp12) file with 10 Tables and accessed via a Hosting Service with Webdirect.

The Database requires that you need to be able to copy Data from a number of fields in one Table to equivalent Fields in another Table for some Records when required.

I can't use the Import Script - Reason being that users will be accessing Filemaker as a Hosted Database through Webdirect and it keeps prompting for an Import file.
The same Import Script works fine in our Single License of Filemaker Pro 14.
Is there any other way I can get Import to work with Webdirect?

As a workaround (because of WebDirect) I have created another equivalent Script but using Copy & Paste (with a Go to Layout between the 2 Tables) for all the fields required and it works fine but there are a large number of script lines to add to complete the same task when compared to the Import Script.

Another reason for the large number of Script lines and using Copy/Paste is that the Records contain about 10 Repeating fields with 30 Repetitions for each field (a legacy of an old Filemaker 3 Database) and Copy/Paste only copies the data in the first line of the Repeating Field so have split each repeating field into 30 separate fields.

It's not a major issue as they will be once off Scripts, with probably few changes to it going forward, but I was just wondering is there a better way of doing this.

This probably sounds a very crude way of doing things to all of you who know Filemaker inside out, but I only work with Filemaker now and again and have a limited knowledge of Script commands and options.

 

Link to post
Share on other sites

Instead of copy/paste, you should be using variables. That is true in general, not only for the situation you describe. 

Note that variables can have repetitions too - so you can use a loop to populate 30 variables with the values of a repeating field, and another loop to set the 30 repetitions of the target field.

Another option worth mentioning is to perform the operation on the server - but you would have to split it into export and import.

On a more general note, I would suggest you re-examine the need for copying data from one location to another. This is rarely required and usually indicates poor design.

 

Edited by comment
Link to post
Share on other sites

Thank you for your Reply and I appreciate you taking the time to Answer.

As mentioned I'm not that familiar with Filemaker scripts and functions (just the basics, calculations, Import etc)

In the Old Filemaker 6 Files (Hosted on a Local Server), a Script to "Generate Sales Orders" was used to Import some of the Fields Data from a Record in one File (Quotes) to equivalent Fields in a new Record in another File (Sales Orders) and I was able to replicate this in Filemaker 14 but couldn't do so when using Webdirect.

So for example in the "Quotes" Table you would have a number of Fields/Repeating Fields (like below) which would be imported to equivalent fields in the "Sales Orders" Table where the Data could also edited if required.

Fields such as - Company Name, Address, Phone No. etc


Repeating Fields -

Item No.    Description    Quantity     Price    Total Price

1                 Product 1         3               £10           £30    

2                Product 2         4               £15           £60

'
'
30             Product 30       2              £20           £40


So a further query

(1) Would Copy/Paste work Ok for this scenario and I know you mentioned it's bad design and not the done thing but I've inherited this configuration so have to try and work with it for the moment.

(2) As I'm not that familiar with Filemaker variables (but will learn) how would I apply it to the above scenario. Can you point me to any Links that have Scripts with Variable examples.

(3) Would the Export (from Quotes) Import (to Sales) require any additional Services from the Filemaker Hosting Company as we will just have a Basic Hosted Single Database and can it also be done with "No Dialog" so that no intervention from the User is required.

Link to post
Share on other sites
4 hours ago, Netcomm said:

(1) Would Copy/Paste work Ok for this scenario

I thought you already had copy/paste working. The problem with copy/paste is that (a) it destroys the user's clipboard, and (b) you only have one clipboard. That means you need to copy each field in turn, go to the target layout, find the target field, paste it there, come back to the source layout and go to the next field - which is both tedious and slow. 

 

4 hours ago, Netcomm said:

(2) As I'm not that familiar with Filemaker variables (but will learn) how would I apply it to the above scenario.

Roughly (assuming you are already on the source record in Quotes):

Set Variable [ $companyName; Value: Quotes::Company Name ]
Set Variable [ $address; Value: Quotes::Address ]
Set Variable [ $phone; Value: Quotes::Phone No. ]
Loop 
   Set Variable [ $i; Value: $i + 1 ]
   Exit Loop If [ $i > 30 ]
   Set Variable [ $item[$i]; Value: Quotes::Item No.[$i] ]
   Set Variable [ $description[$i]; Value: Quotes::Description[$i] ]
   Set Variable [ $qty; Value[$i]: Quotes::Quantity[$i] ]
   Set Variable [ $price[$i]; Value: Quotes::Price[$i] ]
End Loop
Go to Layout [ Sales Orders ]
New Record
Set Field [ Sales Orders::Company Name ; $companyName ]
Set Field [ Sales Orders::Address ; $address ]
Set Field [ Sales Orders::Phone No. ; $phone ]
Loop 
   Set Variable [ $j; Value: $j + 1 ]
   Exit Loop If [ $j > 30 ]
   Set Field [ Sales Orders::Item No.[$j] ; $item[$j] ]
   Set Field [ Sales Orders::Description[$j] ; $description[$j ]
   Set Field [ Sales Orders::Quantity[$j] ; $qty[$j]: ]
   Set Field [ Sales Orders::Price[$j] ; $price[$j] ]
End Loop
Commit Records


 

6 hours ago, comment said:

Another option worth mentioning is to perform the operation on the server

On second thought, that wouldn't work for you because server-side scripts cannot export to .fmp12 format - and you have repeating fields.

 

Edited by comment
  • Like 1
Link to post
Share on other sites

Yes Copy/Paste is working OK  - I should have clarified my question a lot better - meant to say "would Copy/paste be Ok for this scenario" (in other words  copy/pasting and changing layouts maybe up to 150 times for 1 record with the Flemaker file hosted on a remote Server and accessing through Webdirect).

I'll probably time in locally and then remotely and compare.

 

I will check out the Set Variable option and I really appreciate you taking the time to write out that script code. It's great to see that there are members that go out of their way to help other members that are fairly new to Filemaker.

 

Thanks again for your Help

Link to post
Share on other sites

The Set variable works perfect for the single fields and brought the correct data across.
However it didn't work for the repeating fields.

In the Repeating field section I was unable to set variable for the item, desc, qty or price as per the script part below.

Loop
   Set Variable [ $i; Value: $i + 1 ]
   Exit Loop If [ $i > 30 ]
   Set Variable [ $item[$i]; Value: Quotes::Item No.[$i] ]
   Set Variable [ $description[$i]; Value: Quotes::Description[$i] ]
   Set Variable [ $qty; Value[$i]: Quotes::Quantity[$i] ]
   Set Variable [ $price[$i]; Value: Quotes::Price[$i] ]
End Loop

For exanple in Set Variable [ $item[$i]; Value: Quotes::Item No.[$i] ]  
It wouldn't allow the [$i] in the variable Name so not sure where the first [$i] goes

It only allowed me to enter  Set Variable [ $item; Value: Quotes::Item No.[$i] ]

Link to post
Share on other sites
2 hours ago, Netcomm said:

It wouldn't allow the [$i] in the variable Name

The [$i] doesn't go in variable name. There's a dedicated Repetition field for it:image.png.97c666df78c66282b06c29a7146e4bb3.png

 

 

Link to post
Share on other sites

It now works perfectly for all fields including the repeating fields

Thank you for the Script code clarification above.

 

I really appreciate all your time spent on it.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.