Inserting new data for related table in form layout

[Lala]

Hi,

I am in the process of creating a relational database for our research lab and came up with a very basic problem:

[color:red]I can't seem to enter in data for a new record in the form view of one of my layouts.

This is the basic design of our database. We have several tables that are related to each other by one id, the master ID. I've created a subject_contact_info table and scheduling_info table. I matched the Master_ID fields on the relationship graph, and I created forms for subject information and scheduling information. Now, here's my problem. After I go to form view and entered in data for the subject_information_form, I click on the scheduling_form (via button) and try to enter in data for new schedules. Filemaker won't allow me to enter in any new data! Instead, it generates another record in the subject_information_form. Please help me address this problem! It feels like I'm so close to finishing the database, yet I can't get this one basic issue down correctly.

[Lala]

Hi. Thank you for the response. I consulted a friend about the issue and ended up redoing my entire database. I have a client table and a schedules table. The client table has a 1 to many relationship with the schedules table and I have allowed for the creation of related records in the schedules table. I decided that I want to create new records in the schedules table in the Clients form. However, how can I ensure that the records would be related to the client that I want it to be related to?

[Lala]

So far, this is what I'm considering.

1. Make a portal of the Schedules table in the Clients form. Create a script that would take you to the related record from the portal. (What script would I use then?)

2. First method doesn't sound as intuitive for users. Can I somehow add a related record in the Schedules form? Those changes are then reflected in the portal in the Clients form. But how would I go about doing this? (which brings me to my initial question?)

[Lala]

The problem with creating new Schedules in the portal is that I have too many fields in my Schedules table to fit in a portal. To address this problem, I created a button that would take me to the Schedules Form and create a related record by copying the primarykey from the Clients table into the foreign key field in the Schedules table.

Here is the script:

Go to Layout ["Schedules_Form" (tblSchedules)]

New Record/Requests

Set Field [tblSchedules:ClientIDfk]

Insert from Last Visited [select; tblClients::ClientID]

The button worked fine the first time I tried it (which was days ago). However, it doesn't work today and I can't seem to figure out why. :

[Lala]

Thanks for the reply. I made the changes you suggested, but somehow the ClientID(fk) field on my Schedules form is not filled.

Any suggestions?

[LaRetta]

Here is visually what should happen:

You are on your Clients form. You want to create a Schedule for the Client. You click a button which:

1) grabs the ClientID from the current client record and stores it in a variable

2) Switches layouts to the Schedules table.

3) Creates a new Schedules record

4) Sets the Schedules ClientID field with the variable

Now ... you have FMS Advanced. So go to your Clients layout then turn on debugger (Tools > Script Debugger). Also open your Data Viewer ( Tools > Data Viewer). Click the Watch tab and then the green plus and add $ClientID.

Now when you run your script, you can watch exactly what happens (or doesn't happen). I suspect that you are setting the variable with different text than you are calling the variable on the other end. In other words, named variables are text objects and if you set a variable with $client ID (with a space) but you later try to retrieve it with $ClientID (no space) then it won't work. Make sure you named the variable the same in both places.

See if the ClientID is moved into the $ClientID in data viewer. You can walk through the whole process and see exactly what breaks. You will ADORE the power at your fingertips to see EXACTLY what is happening in your scripts and variables.

Let us know how it goes. :^)

Edited July 29, 2009 by Guest

[LaRetta]

BTW, variable names are NOT case sensitive but spelling and spaces must match exactly.

[Lala]

I typed in your script as follows:

Set Variable [$Client_ID; Value:tblClients::ClientID]

Go to Layout ["Schedules_Form" (tblSchedules)]

New Record/Request

Set Field [tblSchedules::ClientIDfk; [color:red]tblClients::ClientID]

Go to Field [tblSchedules::schDate]

I'm not quite sure I understand what you mean when you say that BOTH named variables must match. When I clicked on "Set Variable" in the Scriptmaker, i made the following specifications:

Named: $Client_ID

Value: tblClients::ClientID

When I click on the Set Field script, I get:

Specify target Field : (I chose ClientIDfk from the Schedules table)

Calculated result: tblClients::ClientID

The data viewer didn't show any error with this script and didn't enter the set variable during the set field script step. Am I supposed to type in tblClients::ClientID =$Client_ID in the calculated result box? I tried that, but it didn't work.

My partner experimented with another script and it appears to work just fine. Here is the script:

Copy [select; tblClients::ClientID]

Go to Layout ["Schedules_Form" (tblSchedules)]

New Record/Request

Paste [select; tblSchedules::ClientIDfk]

Go to Field [tblSchedules::schDate]

What are the limitations for this script? Would the script be "broken" if we changed the layout?

[Lala]

Thanks a bunch! I typed $Client_ID in the text box for calculated value and that gave me the desired result. Case closed..