Create New Record from Related Database

[steigrafx]

I have two databases related by the fields:

File_No = File_No

Date = Date

(Taken together, all records are unique.)

The databases are called Electronic_Recording and Transcript_Requests. If someone requests a transcript of a recording after an employee enters a record in Electronic_Recording, someone else creates a new record in Transcript_Requests. As soon as they enter the File_No and Date, most of the fields populate by "lookup."

Quite often, a transcript is requested immediately. I was wondering if there is a way to script a new record in the Transcript_Requests database from within the open Electronic_Recording file. In other words, if an employee creates an Electronic_Recording record, is it possible to run a script that opens the Transcript_Requests database and populates the File_No and Date fields in a new record automatically?

If you could point me in the right direction I’d appreciate it, as I’ve tried writing a script in Transcript_Requests and "calling" it from Electronic_Recording, but I can’t figure out how to pass the contents of the two fields to the new record. The fields always end up empty.

Thank you,

Kevin

[Ron Cates]

You would need a script that sets the File_No and Date to variables. Then go to a layout based on the Transcript_Requests table. Create the new record and use set field to enter the $File_No and $Date values from the variables. Then Goto Original layout. It may sound clunky to have to goto another layout and back again but the script executes faster than you can see the layouts change.

P.S. It is highly recomended that you use auto enter serial numbers for relationship keys rather than data fields. This insures that the values are always unique and unchanged. Anything else, ( other than some advanced UUID techniques ) will invariably lead to trouble down the road.

[steigrafx]

Thank you again, Ron. I took your and comment's advice with regard to serial numbers in my post yesterday in the Calculation forum (if you recall).

Thanks again,

Kevin

[steigrafx]

Ron, I tried to follow your steps but I am unsuccessful. I haven't yet instituted your recommendation to use auto enter serial numbers -- I'm new to databases and at this point I'm experimenting and slowly learning and fine-tuning. If I can get this to work, the next step will be to use serial numbers.

The first script (in the Electronic Recording file) sets the global variables and then opens and performs a script in the Transcript Requests file, and that script should pass the variables, but doesn't. I'm obviously doing something wrong.

I'd appreciate it if you would take a look at my scripts to see where I've gone wrong.

In Electronic Recording:

(Script called NewTRrecord)

Set Variable [$$FileNo; Value:Electronic Recording::File_No.:]

Set Variable [$$Date; Value:Electronic Recording::Date.:]

Open File ["Transcript Requests"]

Perform Script ["NewRecordFromER" from file: "Transcript Requests"]

In Transcript Requests:

(Script called NewRecordFromER)

New Record/Request

Set Field [Transcript Requests::File No.:; $$FileNo]

Set Field [Transcript Requests::Date:; $$Date]

Thanks,

Kevin

[efen]

The global variables empty when the script goes to the other file. Use globals fields instead.

[Ron Cates]

@efen

Are global variables restricted to the current file? I've only ever worked with a single file system.

@steigrafx

If that is the case you will need global fields to hold the variables. Global fields will work either way.

Also in this line you are setting the date variable equal to File_No. Probably just a typo but i thought i would point it out.

Set Variable [$$Date; Value:Electronic Recording::File_No.:]

Wondering if you would be better off combining both files or even creating a table in Electronic Recording for Transcript Requests that uses the table in the Transcript Requests file as an external data sourse?

[steigrafx]

Thank you, both. I'll try the global field route first. I didn't know that global variables empty. When I have time, I will consider combining the two files.

And yes, Ron, that was a typo.

Have a good day, both of you.

Kevin

EDIT: I fixed the typo.

[LaRetta]

You can pass global variables in script parameters to another script or another script in another file.

[comment]

You can pass global variables in script parameters

That's not very well put, is it? I mean, you don't pass global variables in script parameters, you pass values in script parameters. The values can come from anywhere - but a global variable in this file cannot be read in the other file.

[LaRetta]

No, it was not put very well. I do mean values can be passed on.

Proper wording is very important!

[steigrafx]

I'm not sure I understand, but I am sure that I don't understand.

How is this accomplished? Please nudge me in the right direction.

I wish there was a good book on Filemaker Pro 11. Do you know of any?

Thanks,

Kevin

[LaRetta]

The first script (in the Electronic Recording file) sets the global variables and then opens and performs a script in the Transcript Requests file, and that script should pass the variables, but doesn't.

Please see pseudo-script changes below. Back up always before trying something new.

In Electronic Recording:

(Script called NewTRrecord)

Set Variable [$$FileNo; Value:Electronic Recording::File_No.:]

Set Variable [$$Date; Value:Electronic Recording::Date.:]

Open File ["Transcript Requests"]

Perform Script ["NewRecordFromER" from file: "Transcript Requests"] ...insert script parameter in blue below here

You can set the script parameter directly with the values in this instance because you are still on table occurrence of Electronic Recording (if I understand your script correctly; not sure where you are collecting these values). If not on Electronic Recording layout then you will need to pass the variable values instead of using the fields themselves.

Script parameter would be:

Value:Electronic Recording::File_No

& "|" &

Value:Electronic Recording::Date

In Transcript Requests:

(Script called NewRecordFromER)

Set Variable [ $p ; Get ( ScriptParameter ) ]

New Record/Request

Set Field [Transcript Requests::File No.:; Let ( pos = Position ( $p ; "|" ; 1 ; 1 ) ; Left ( $p ; pos -1 ) ) ]

Set Field [Transcript Requests::Date:; Let ( pos = Position ( $p ; "|" ; 1 ; 1 ) ; Right( $p ; Length ( $p ) - pos ) ) ]

Commit Record/Request ... etc

[steigrafx]

Thank you, LaRetta. I'm learning bit by bit. I'll try this either tonight or tomorrow morning and report back.

I appreciate your help as well as your time.

Thank you,

Kevin

[LaRetta]

I got lazy, Kevin, because I had copied your script and just changed it. But notice on the script parameter, I had forgotten to remove the 'value:' portion (and I now have strikeout through them). The single calculation will be simply the fields separated by pipe character as:

Electronic Recording::File_No

& "|" &

Electronic Recording::Date

[steigrafx]

Yes, I figured that out as I was entering it.

I couldn't wait to try it. It works like a charm -- now I just have to "dissect" it to figure out exactly how that works. I suppose it's time to crack open "The Missing Manual".

Thanks, LaRetta, for your generosity.

Kevin