Sign in to follow this  
Followers 0

Copying records from one table to another

22 posts in this topic

Posted

Hello All:

I know the basics of writing scripts only, and started having problems adapting a script I saw in one of the forums.

Basically, what I want to do is copy from a template file a set of commonly used part numbers and part names to new records in a table.

At the same time I would like to populate a field of each newly created record with a value to enable the relationship to the main table record.

I'm specifically having problems with:

- how to start and stop a loop, how to increment it--I am discovering that loops in FileMaker scripts does not quite resemble similar constructs in computer languages I have been exposed to over the years.

- copy a value from a field in a table to another field in a different table using the "Set Field" script step.

Structure of my database:

Main table: Equipment_Repaired

Table 2: Parts_Used

Table 3: Parts_Template

Parts_Used is related to Equipment_Repaired and is shown in a portal.

Parts_Template is a standalone table that contains a list of parts and descriptions that will be copied each time a record to the Equipment_Repaired is added in a portal.

Thanks for all help in advance.

Sam

Share this post


Link to post
Share on other sites

Posted

- how to start and stop a loop, how to increment it

Here is a typical example:

Here's an example from the online help.

Set variable[$i;0]

Loop

 Set Variable[$i;$i+1]

 Exit Loop If[$i>10] // or some other defined condition

End Loop



The other way to exit a loop is with Exit Loop After Last.

- copy a value from a field in a table to another field in a different table using the "Set Field" script step.
It's often most convenient to use a variable as an intermediate step. E.g.:

Set Variable[$part;myPartField]

Go to Layout[where your target field is]

Set Field[myTargetField;$part]

Share this post


Link to post
Share on other sites

Posted

Thank you, Tom. Your example makes it much clearer.

The number of parts in the "Parts_Template" will change over time. So I cannot use a fixed value for the condition in the "If" statement.

Is there a way of either:

- counting the number of records in Parts_Template and then passing this on as a variable to the if statement, or

- incrementing the loop until the end of Parts_Template is reached?

Sam

Share this post


Link to post
Share on other sites

Posted

Why loop it in the first place since, when import between tables actually is possible? Take a look at the template I link to in this post:

http://fmforums.com/forum/showpost.php?post/278585/

--sd

Share this post


Link to post
Share on other sites

Posted (edited)

Hello Soren,

I asked in a different forum of import between tables is possible, and was waiting an an answer on it.

Thanks for pointing out that it *is* possible.

I tried to reach the link in the post you referred to but my browser could not find the computer:

[color:red]-- UPDATE: Never mind, I am now able to reach dwdataconcepts! Sam

Navigator can't find the server at www.dwdataconcepts.com.

Is it possible for you to post the script here?

Sam

Edited by

Share this post


Link to post
Share on other sites

Posted (edited)

Well, I looked at Soren's examples. Very useful for a beginner like myself.

I also looked to see if there are any answers to my inquiry about copying from table to table. To my dismay I found out that my inquiry somehow was not posted.

Here is what I want to know:

On the server all my tables appear as part of a single database called "Equipment_Maintenance". I am not the server administrator but I am guessing that this is all contained in my single file that I gave to the administrator some months ago.

I can see that one can import from a table that is in a single standalone file, but how do you do this thing in my case?

"Specify" option in "Import Records" script step allows only references to external files.

Suppose I refer to fmpnet://fmpserver/Equipment_Maintenance in the "specify" option.

How do I then indicate the table? What is the syntax?

Thanks,

Sam

Edited by
Correct typo

Share this post


Link to post
Share on other sites

Posted

"Specify" option in "Import Records" script step allows only references to external files.

Not quite true although it seems so, try to grab the file itself via the dialog, and remember to point at the right table in the mapping dialog.

--sd

Share this post


Link to post
Share on other sites

Posted

Hello Soren:

I just discovered that myself a few minutes ago.

Will it remember the table and the field mapping when I place this script on a button?

Sam

Share this post


Link to post
Share on other sites

Posted

Yes [restore] is shown when it is in the script.

--sd

Share this post


Link to post
Share on other sites

Posted (edited)

I figured it out. Thanks.

I will now work on populating the field that establishes the relation to the main table.

This I think will require a loop:

- Find all new records,

- Loop through each and enter the "Repair Ticket Number" to the appropriate field. This value is already on the main table.

Let's see if I can figure this out.

Sam

Edited by

Share this post


Link to post
Share on other sites

Posted

Last question of the day:

Well, I discovered that I don't have to use a loop.

However, I am having difficulty with the "Replace Field Contents" script step. Here is what I am doing:

- I am first performing a find on all the records that contain the word "new" in the "repair_ticket_from_main" field.

- Then I am using the "Replace Field Contents" with the following parameters:

. Specify Target Field: repair_ticket_from_main (in the Parts_Used table)

. The other specify button below: I am choosing "Replace with calculated results" and under "Specify" for this option I have "GetField ( "Equipment_Repaired:: repair_ticket" )

I do not have anything else except the GetField function.

Is this how I am supposed to use this particular script step?

As always, appreciate all the help I'm getting!

Sam

I will now work on populating the field that establishes the relation to the main table.

This I think will require a loop:

- Find all new records,

- Loop through each and enter the "Repair Ticket Number" to the appropriate field. This value is already on the main table.

Let's see if I can figure this out.

Sam

Share this post


Link to post
Share on other sites

Posted

Otherwise give me a shout!

--sd

Share this post


Link to post
Share on other sites

Posted

Soren,

One last thing: I am still having problems with the issue I wrote in a previous post:

http://fmforums.com/forum/showpost.php?post/278619/

I would appreciate your comments. Of course, other folks can chime in as well! :laugh:

Sam

Share this post


Link to post
Share on other sites

Posted

It's not absolutely clear if the two tables in any way are related, if not will it due to the quotes inside the GetField( command be confused enough to hand out a question mark instead of the field's value.

You need to explain why you use GetField( in the first place it has the same meaning as referring directly to the field, opposed to the use without quotation marks, where it's meant to be working, you have a field-value where you store a string indicating which field the value should be plucked from ...via indirection.

Where it has it uses is summary reports with globals plucking fields to each column, in my humble opinion not in replaces unless you wish to do something like the attached template, but the template includes:

http://www.filemaker.com/help/Script-Steps36.html

...as well which kind of makes the following lines in the scripting waste of time to implement. But what it does is to fill first column with "a"-s and then go to the next column and get the values from the previously filled fields, then when that is done go further to the next column and do the same.

--sd

Etude.zip

Share this post


Link to post
Share on other sites

Posted

Well, I studied your example. Some of the things are over my head at his point, but I tried to apply some of them.

The script is importing properly, but when it comes to changing the text "new" to the "Repair Ticket No" from the Equipment_Repaired table, it is failing.

I am attaching a simplified version of the DB. The red button on the form that displays the portal is running the version of the script that does not work.

The green button has a version of the script that just imports the parts, but does not attempt to change the text "new".

I would appreciate if you can comment on what I got.

Sam

test_import.zip

Share this post


Link to post
Share on other sites

Posted

Alright this is a hen/egg question, how would the newly imported record know which records it's related when they aren't related yet.

But there's more to it than that, how likely are you part-numbers to be reassigned to entirely different things? You are importing way too much info, which over time will make your file a whopping big one. The template TO need to get tied to the structure as well ... to prevent the redundance by ushering in the part description as referenced text.

--sd

test_importMOD.zip

Share this post


Link to post
Share on other sites

Posted (edited)

- "how would the newly imported record know which records it's related when they aren't related yet.": The text "new" will only appear for the newly inported ones, which will immediately replaced by the appropriate Repair Ticket No. This will establish the relationship.

- "part-numbers to be reassigned to entirely different things." Never. Actaully, in the final version of the DB I am thinking of using a different master table to look up the part name rather than importing it.

I looked at your changes, thank you!

Sam

Edited by
Clarifying the response.

Share this post


Link to post
Share on other sites

Posted (edited)

Instead of the Perform Find/Replace, could the foreign key be an autoenter value via a $variable, which will establish the relation as soon as the found set arrives via the import.

Just pay attention to the above dialog when mapping is done!

--sd

dialog.jpg

test_importMod2.zip

Edited by

Share this post


Link to post
Share on other sites

Posted (edited)

"Instead of the Perform Find/Replace, could the foreign key be an autoenter value via a $variable, which will establish the relation as soon as the found set arrives via the import."

I like this idea, but i would not know how to do it while importing!

Give me some hints... Maybe using "Insert Text"?

Thanks!

Sam

Edited by

Share this post


Link to post
Share on other sites

Posted

I modified the above message again - Sorry!

Share this post


Link to post
Share on other sites

Posted

I will start looking at it right away.

Share this post


Link to post
Share on other sites

Posted

Soren and the rest of the folks who chimed in from time to time:

I implemented the things we discussed in the forum in the production version of my DB. It is working like a charm.

Many thanks again!

Sam

Share this post


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
Sign in to follow this  
Followers 0