Copying records from one table to another

[SamMc]

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

[Fitch]

- 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]

[SamMc]

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

[Søren Dyhr]

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

[SamMc]

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 January 19, 2008 by Guest

[SamMc]

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 January 20, 2008 by Guest
Correct typo

[Søren Dyhr]

"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

[SamMc]

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

[Søren Dyhr]

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

--sd

[SamMc]

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 January 20, 2008 by Guest

[SamMc]

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

[Søren Dyhr]

Otherwise give me a shout!

--sd

[SamMc]

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!

Sam

[Søren Dyhr]

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

[SamMc]

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

[Søren Dyhr]

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

[SamMc]

- "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 January 21, 2008 by Guest
Clarifying the response.

[Søren Dyhr]

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

test_importMod2.zip

Edited January 21, 2008 by Guest

[SamMc]

"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 January 21, 2008 by Guest

[Søren Dyhr]

I modified the above message again - Sorry!

[SamMc]

I will start looking at it right away.

[SamMc]

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