New to scripting

[PinkFloyd315]

I have a database in FM 7 that has 3 related tables. The main file has basic info such as patient#, age, gender, etc. I have created the associated tables with a relationship using patient#. The 3 related tables are info from 3 visits to the office for pre-op, post-op and 3 month followup.

On the main data entry layout where we gather the basic info mentioned above I have 3 buttons that call the other 3 layouts for data entry into the associated tables.

I want to enter the data in the main layout and then click a button to load the second data entry layout but to check for the patient# in the new table and pull the data in if it exists and to create a new record if the patient# is not found and automatically enter the patient# from the main layout into the appropriate field (in the associated table) and force a jump to the next data entry field, which will also force data from the main table to be displayed at the top of the new layout so that the data entry person can verify they have the correct patient.

I have all of the layouts functioning properly I just need to get the scripts written to do the steps mentioned above.

HELP!

[LaRetta]

Hi PinkFloyd315,

"The 3 related tables are info from 3 visits to the office for pre-op, post-op and 3 month followup."

I hate to mention this but I think you will find that, since these are all visits, they should be in the SAME table - a Visit table. A VisitType field (popup or checkbox of pre-op, post-op and followup) distinguishes one visit from the other. As you've currently structured it, you will find yourself hitting brick walls when attempting to organize, report and expand.

Having said that, the easiest way to accomplish what you wish is to let FileMaker handle this for you by turning on the 'Allow Creation of Related' feature in your relationship definition (in the graph). If 'Allow Creation of Related' is checked then entering data in any Visits field (EXCEPT THE PATIENT#) will create the new record for that Patient (if it doesn't already exist) and AUTOMATICALLY insert the Patient# in the related Patient# field.

In your case, from the Patient layout, you could place a button called 'create visit.' Script would simply be:

Set Field [ yourRelatedTable::VisitDate ; Get(CurrentDate) ] ... or ANY related field but Patient#

Commit Record

Go To Related Record [ yourRelatedTable layout; using your pre-op layout ; same window ]

Go To Field [ first field to start entering data ]

When user gets to the new layout, the record will already be created (from the Set Field[]) and the patient data will already appear. Allow Creation rocks ...

Alternately (if you skip Allow Creation), your button could hold script parameter with the Patient#. Script would then be:

Go To Layout [ pre-op layout ]

New Record/Request

Set Field [ Patient# ; Get ( ScriptParameter) ]

LaRetta

[comment]

since these are all visits, they should be in the SAME table

I am not too sure about that - it might very well be that the actual fields are very different for the different types of visit.

As for navigation, I would make the relationships allow creation of new records.

Then, put the three DateOfVisit fields on Patient layout, with a button next to each.

The buttons are defined as Go to Related Record (each pointing to a layout of the corresponding visits table).

Filling the (related) date field creates a record in the appropriate visits table.

Clicking the button takes you there. As long as the date has not been filled out, the button does nothing.

No scripts required, and you get a nice overview of the patient's progress on the main layout.

[PinkFloyd315]

Hi LaRetta;

Thanks for responding.

The 3 visit tables are actually for entering questionnaire information to track changes in patient's condition so we would like to have them kept seperate.

The information is entered by a data entry person and not necessarily on the same day as the visit so we cannot use CurrentDate.

I like your suggestion of the 'Allow Creation of Related' feature and will give that a try. What will happen if the record already exists in the 2nd table?

Take care and have a great day.

[LaRetta]

"The information is entered by a data entry person and not necessarily on the same day as the visit so we cannot use CurrentDate."

This is not a problem. You don't need to set using the current date - your script can set ANY related field (or User can enter ANY related info) to create the record. I prefer Michael's idea. It is much more intuitive, provides valuable information on the patient layout and eliminates need for script but ... a Patient can have many pre-op records so these relationships can all be one-to-many?

At this point, many on forums would simply capture the Patient#, jump to related layout and create a new record via script. But there are more elegant ways by using a related tempID to create the new record. I would prefer deferring to Michael (Comment) on anything regarding relationships ... heck, on anything in fact.

I can provide you with methods that work. Michael can provide you with elegant, lean solutions that work. The choice is yours.

LaRetta

[PinkFloyd315]

Hi comment;

I thank you very much for your suggestions. I used the date entered field as the record creation link on the main info page and it worked perfectly.

It is great to know that there are such knowledgeable people out there that are willing to help others and share their wisdom. It is greatly appreciated. Thanks to LaRetta for her support as well.