Jump to content

  •  

Photo

Copying records from one table to another


  • Please log in to reply
21 replies to this topic

#1 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 18 January 2008 - 11:48 PM

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
  • 0

#2 Fitch  Imaginary friend

Fitch
  • Moderators
  • 4,037 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12, 13
  • Membership:TechNet
  • Time Online: 16d 12h 51m 5s

Posted 19 January 2008 - 12:12 AM

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

Here is a typical example:
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.
Here's an example from the online help.

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

  • 0
Tom Fitch :: Portland, Oregon :: Fitch & Fitch: FileMaker consulting

#3 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 12:29 AM

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
  • 0

#4 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 19 January 2008 - 11:35 AM

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/...hp?post/278585/

--sd
  • 0

#5 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 12:08 PM

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:

-- 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
  • 0

#6 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 02:53 PM

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
  • 0

#7 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 19 January 2008 - 02:58 PM

"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
  • 0

#8 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 04:06 PM

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
  • 0

#9 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 19 January 2008 - 04:11 PM

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

--sd
  • 0

#10 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 04:31 PM

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
  • 0

#11 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 19 January 2008 - 05:20 PM

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


  • 0

#12 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 20 January 2008 - 01:55 AM

Otherwise give me a shout!

--sd
  • 0

#13 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 20 January 2008 - 12:03 PM

Soren,

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

http://fmforums.com/...hp?post/278619/

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

Sam
  • 0

#14 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 20 January 2008 - 01:08 PM

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...pt-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

Attached Files


  • 0

#15 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 20 January 2008 - 11:45 PM

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

Attached Files


  • 0

#16 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 21 January 2008 - 01:08 AM

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

Attached Files


  • 0

#17 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 21 January 2008 - 11:00 AM

- "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
  • 0

#18 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 21 January 2008 - 12:02 PM

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

Attached Thumbnails

  • 1200946446-dialog.jpg

Attached Files


  • 0

#19 SamMc  novice

SamMc
  • Members
  • 16 posts
  • FM Application:8.5 Advance
  • :

Posted 21 January 2008 - 12:14 PM

"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
  • 0

#20 Søren Dyhr  Carpal Tunnel

Søren Dyhr
  • Members
  • 6,230 posts
  • FM Application:10 Advance
  • Time Online: 8h 5m 2s

Posted 21 January 2008 - 12:16 PM

I modified the above message again - Sorry!
  • 0




FMForum Advertisers