Script to copy data then enter into second file

[AntBlabby]

I am a novice at this real Filemaker stuff, my 10 year experience limited to making mailing labels and tracking addresses and phone numbers, but usually I learn by doing, so here I am. And I bought a book, but it seems pretty confusing to read. So here's what I am trying to do, and I apologize if this actually is simple. I am trying to write a script that opens up a second file, opens up a new record and enters data from a field in the first file into a particular field in the second file. I thought the way to do it is to write a script that first 1) copies the data in the field from the first file, 2) opens the second file, 3) creates a new record, 4) goes to the field I want and pastes the info from the other file that I've copied. Where I get hung up is that when i use the wizard, there is no way (or so it seems) to choose the field name from the second file. Is there some other way to do this??? I assume that there must be because this must be a common usage. Thanks from Sunny Southern California!

[Fitch]

It's better to use global fields to move data from one file to another.

In File A:

Set field (globalText, someData)

Perform script (external, File :

In File B:

Enter Browse mode

New record

Set field (theTarget, anyFileArelation::globalText)

With global fields, you can use any relationship from one file to another (even a non-valid one!), you don't mess up the user's clipboard, and you don't have to rely on a particular layout.

[spb]

Note the use of Set Field in Tom's example. Beware of using the various Copy, Insert, Paste functions. Set Field does the job cleaner & with greater certainty than the others.

In your case, you might set up a constant relationship. Make a new field in each database. Make it a calculation field & make the calculation to be simply the numeral 1. Call these fields "Constant." The fields will always contain a one, across all records. You can store the fields on an All Fields layout. No need to view them again. Set up relationships in both directions, with these constant fields as the key fields.

Now you can use this relationship to grab the contents of the global field Tom described, without worrying about whether you are in synch with your records.

If you are doing this to only one record at a time, it's fairly simple. If you wish to do this to a group in the first database (say, make 12 records from a found set of twelve), then you'll need a looping script that goes to the first of the twelve, puts your field contents into the global, activates the script in the other database to make a record & set the field from the global. Then it will progress to the second record, overlaying the global with the new info, & etc.

Unless you want to, you won't even need to see the second database. The next time you open it, you will find it is 12 records larger, with the correct info in the correct field(s).

This is a very basic form of the standard technique for making line item orders from an inventory database. I've had a bit of experience with this, so feel free to ask for clarification.

There are several other ways to do this job, assuming multiple records, like make a dozen new records at once, then importing the field contents. But though it may seem cumbersome at first, the above technique is extremely flexible and adaptable to a variety of problems.

Steve Brown

[-Queue-]

If you're only going to have one record in the related file per record in the main file, you can use a unique relationship between the two (from serial in the main file to MainFile_key (for example) in the other), with 'Allow creation of related records' checked. Then when you Set Field ["relationship::somefield", "field"] from the main file, if the relationship is not yet valid (i.e. there isn't a match in the related file), FileMaker will force it to be valid (create a new related record) and enter data into somefield as specified. If the relationship is valid, then the Set Field will enter the data without creating a new record.

This technique can be used to kill several birds with one stone, as it were. No globals are necessary. And the first Set Field populates the key field implicitly, allowing Set Field ["relationship::whatever", "this"] to populate both the key field and whatever simultaneously.

If there can be multiple related records per record in the main file, then you can use a similar technique. See attached for an example.

MultipleRelateds.zip

[AntBlabby]

Thank you all...I definitely have to read this all and absorb it. I am not up to speed. I am trying to understand why y'all recommend global fields. I thought it was the same operation throughout a database. Therefore in text, it would be the same thing data over and over in each record, no? I also am also trying to understand Set Field, which seems to be a paste function. This all started because we bought Quickbooks for our dental office which has a Mac, and it sucks big time. Talk about overblown promotion! So I thought I could grow up and learn REAL Filemaker Pro, versus just doing address labels. I Figured that I have done a lot of Excel functions and stuff, so to start with, the field I want to copy is a concatenation of the names, so that it reads, "Doe, John H" which it creates by putting together the three fields, First/MI/Last along with a comma and some blank spaces. Not too fancy. The idea was that Doe, John H could then be the Key relation. I'm new at this relation thing, but it seems that in our office, there are no two patients with the same name. Since we are such hideous typists, I didn't want to retype the name in what I am calling "Patient Register" which will be a running record (with description, item codes, etc.) of treatment provided, charges and payments. I thought that I could do a script that I could attach to a button in what I am calling the "Contact File" which is the name, address, phone, etc. stuff. When a new patient comes in, I would then, after entering the data, click the button and it would open up the Patient Register file, open up a new record and copy the concatenated data in the field, thereby creating a register for that patient in the other file. I suspect that I ought to find a course somewhere for this, that I have exceeded my "teach yourself" abilities....or I have to keep reading. THANK YOU BOTH FOR YOUR HELP SO FAR. I do appreciate that lookups, cuts and pastes, are not the smartest way to do this relational thing. That's immediately apparent from your comments! Regards from the Valley girl!

[bruceR]

Unfortunately you need to rethink things very fundamentally. You do not want to use meaningful fields for relationships, for exactlly the reasons you noted. You need to establish a PatientID field holding a unique meaningless number. If it has meaning, then at some point you will say oops, we mispelled/need to change the format etc.

[Fitch]

It's true, you should never use a name or any other "meaningful" fields for relationships. It might seem like the easy way at the moment, but it's just asking for trouble down the road. You should get in the habit of always creating an automatic serial number field. It should not be modifiable. It's easy enough to add one if you don't have one, just create a text or number field, then click in it, choose Replace, and then choose the serial number option. If you already have related records, you can make a field in the related file and a script that puts the new master serial number in it -- could be a good learning exercise...

As for global fields, maybe I wasn't clear in my first post (ignoring the stuff about relationships): with Set Field, you don't mess up the user's clipboard, and you don't have to rely on a particular layout. E.g., suppose your user want to create a record, and also wants to grab some info from the current screen, or maybe she's copied some text from a Word document. Now she clicks "New," but when she goes to Paste, the info she had copied is gone! You blew it away with your New Record script. Happy user? I don't think so.

The global comes into the picture as simply a convenient way to pass data from one file or one record to another. As you said, data that you put in a global is available throughout a database, regardless of what record you're on. When creating new record in another database, it makes it easy to put data into that record that comes from the original file, because you don't have to worry about what that record's relationship is to the original file, i.e., you're not pulling the data from a particular record -- it's global, it's available from anywhere.

Another way to do it is to set data into globals in the related file, before the new record gets created.

Instead of a global in file A...

In File A:

Set field (globalText, someData)

Perform script (external, File :

In File B:

Enter Browse mode

New record

Set field (theTarget, anyFileArelation::globalText)

...you'd have a global in file B...

In File A:

Set field (anyFileBrelation::globalText, someData)

Perform script (external, File :

In File B:

Enter Browse mode

New record

Set field (theTarget, globalText)

The second reason to use Set Field, having to rely on a particular layout, is easy enough to work around, but it means you will have to be very meticulous as you develop, because if you try to Paste via a script step, but the field you targeted is not on the current layout, no Paste for you!

This is a long post, but look at the actual work involved: one global field, one relationship, and two tiny scripts.

[-Queue-]

If you don't like extra work, wouldn't you prefer my zero global fields, one relationship, one script method?

[Fitch]

-Q-, your script (in the sample file) uses TWO relationships - so there!

The only reason I would argue for my method is that, even though yours is "simpler," I think mine is easier for a beginner to understand.

PS: re-reading the original post, the answer to the basic question, "how to choose the field name from the second file..." You would make a script in the second file that does the Paste. Then in the first file, you use Perform Script (external:second file), and choose that script. Again, that's the easy/basic way, not necessarily the ideal way.

[-Queue-]

I was referring to the posted method, not the attached one.

But since you mention it, the second relationship (in the sample file) is only for testing existence of at least one related record, which is not a necessity for either mine or your methods, merely a 'bonus', if you will. If there will be more than one child record for each parent, then it's superfluous anyway. If you're anal and want to ensure the user doesn't accidentally create a new child, then it's handy.

I agree that it's more theoretically challenging, but if I had learned either technique three years ago, I would have saved much time and wasted sub-scripts, and had much more organized processes. So I would venture to say it's worth the mind-tugging to learn and use. I recently cleaned up a 4 year old invoicing process that utilized sub-scripts in three related files, with redundant steps and rough flow of information. When I was finished with it, it used three subscripts in the main file (for clarity and organization) and none in the child files. Now invoicing is a relatively instantaneous process, whereas users have waited up to 3 minutes previously for a decent-length (5 - 10) page invoice.

Thus, I have no choice but to extol the benefits and wonders of scripted creation of child records from a parent file. And I would encourage even newbies to learn the techniques, even if they don't completely comprehend them. They put you years ahead of the competition.

[AntBlabby]

Thank you all for all your help! I am making progress, but it is slow. I have to say as someone who has a Mac for its ease and graphics that Apple must be applauded for FileMaker Pro which it originally launched. Bravo! Now, if I can get this all to work. Do any of you recommend any particular book to purchase to help me through this learning process?

[Jim McKee]

Queue ...

brilliant method of creating new related records without globals or subscripts or having to create SetField steps in both the parent and child files.

One question: the first bit of your script:

If ["constant|||temp::temp"]

Show Message ["Another record is being created. Try again later.]

Exit Script

End If

Am I correct in assuming that this is for multi-user systems, and that during the fraction of a second it takes to create the new record, the value of constant|||temp::temp = "1", having been set that way by FileMaker to force the relationship to be true? And, thus the need to end the script by setting the field to "0" (zero), to once again set the relationship to false.

Another cool thing about this way of doing it is that you can then have a portal of the child records on the parent record using the main_serial | related_main_serial relationship without being stuck with the "allow creation of related records" attached to the portal -- or being forced to use a clone of that relationship with the "allow creation of related records" turned off.

Thanks for this very kewl trick

[-Queue-]

Hi, Jim. You are correct in assuming the initial test in the script is for multi-user purposes. However, it isn't necessarily just a one-half second time span, depending upon the number of related fields to be set. The example probably wouldn't face this issue, but an actual implementation of it could. Therefore, I included the test to determine whether other users are possibly creating related records at the same time. Since the relationship is based on 1::1, we want to ensure that I (as a user) don't accidentally set another user's fields. Since FileMaker doesn't force the relationship to be valid if it is already, this could be a definite and undesirable possibility.

A solution to this problem is to use a serial_constant::serial_temp relationship. See attached for a modified example.

This is indeed one of my favorite tricks. I only wish I'd learned and implemented it sooner.

MultipleRelatedsv2.zip

[TWillson]

I am also using this method to create related records (just updating some old "create related record" scripts in a multi-user database).

Just a quick question about the initial test in your script. Are there any issues with using a loop to check if a valid relationship exists before proceeding with the creation of a new record rather than exiting the script and forcing the user to re-submit?

Tom

[-Queue-]

Not as long as you use a global to track the number of iterations and provide the exit step after a reasonable number of attempts.

Something like:

Set Field ["gNum", "1"]

Loop

[color:"white"]__ Exit Loop If ["not constant|||temp::temp = 1 or gNum = 50"]

[color:"white"]__ Set Field ["gNum", "gNum + 1"]

End Loop

If ["gNum = 50"]

[color:"white"]__ Show Message ["Another record is being created. Please try again later."]

Else

[color:"white"]__ create related record steps

End If

NOTE: I use If ["rel::constant"] to test for relationship validity and If ["not rel::constant = 1"] to test for invalidity.

[Ugo DI LUCA]

Hi Queue,

I liked your serial::constant tip.

I always tend to use real boolean calcs in my scripts, and constant Numbers. So I was wondering what was your solution offering compared to :

- a boolean IsValid(serial::serial)

which if also stored in the Main file as a calculation IsValid(serial::serial) would offer a way to implement the Visibility tip to hide your "Create Related", by a relationship to a classic Constant Num.

Finally, I must admit I didn't really catched the Multi-User scenario thing.

[-Queue-]

I think it's more a preference than anything else, really. I use Length(field) in place of not IsEmpty(field), for example. Anything to make the code more efficient. I have read somewhere (I can't recall where) that IsValid() is used inappropriately in a relationship test and may not be supported in future FM releases. So I tend to shy away from using it anyway. I think FM's help file supports this idea, too. It only deals with IsValid() testing (relational-wise) whether a file has been renamed or a related field has been deleted, interestingly enough.

The not serial::constant could also be used for an invisible portal button, and I think that is a great idea, as well. I wasn't sure whether more than one child was desired, so I let it go either way in the sample, just to be thorough.

The point I was making about the Multi-User problem was that using a constant::temp relationship could allow one user to modify another's related record. Once the constant::temp relationship has been forced, FileMaker won't do it again until it's necessary. So if user A is creating a related record, the temp field has a value of 1. If user B creates a related record before user A's script has set the temp field to zero, then he wouldn't in fact be creating a new related record, but modifying the one user A had created and was populating. Yikes! This is why I would recommend either the initial test or the second posted version that utilizes the serial_constant::serial_temp relationship.