How to create a record from a relationship

[Maria Tique]

I am able to link my master table to the Background table and the CESD table by carrying over all 3 fields: patient_id, assessid, and visit date. However, if a record exist in the master table and it does not exists in Background or CESD then it should prompt the user

"Would you like to create a new record?"

If user clicks yes, then the Background or CESD table will populate with all 3 fields from the Master Table. Can you please help. ;)

prototype.zip

[Singlequanta]

Hi;

Can you be a bit more specific? I can help you out.

Steve (steve @ grant.cc)

[Maria Tique]

I will further specify and thank you for your help

I have 3 tables: Intake, background, and CESD

Intake table has 4 fields: patient_id, assess_id, visit_date, and comments.

Background table has 3 fields: patient_id, assess_id, and visit_date.

CESD tables has 3 fields: patient_id, assess_id, and visit_date.

In my attached file, you can see I have 3 patient_id’s in the Intake table: 100, 200, and 300.

Currently if a user goes to patient: 100, Assessment 2, 09/09/2005 on the Intake form and clicks on CESD button, then the CESD layout opens and displays patient 100, Assessment 2, 09/09/2005. This is great because ALL 3 fields are carried over from one layout to another.

Currently, patient 100, Assessment 2, 09/09/2005 DOES EXIST in CESD BUT IF this patient DID NOT exist in CESD and the user clicks on the CESD button then I would like the program to have a pop up message and ask the user if they would like to create a record for this patient. If the user clicks YES then patient 100, Assessement 2, 09/098/2005 will automatically exist in CESD.

ALL 3 fields must be carried over from one layout to another.

Please help!!

[Maria Tique]

Would you like for me to be more specific? I hope you can understand this.

[Maria Tique]

Hi,

I have added a script that will prompt the user if they would like to create a new record. My problem is that if the user clicks "Yes" then nothing happens.

For example:

Patient 100 with Assessment 2 and visit date 01/01/2006 only appears in Intake. If the user clicks on CESD button, then a pop up message will prompt the user that the record does not exist and ask the user if they want to create a record. If user clicks "YES" then all 3 fields get carried over from Intake to CESD. I am attaching my file and please help. My background scipt needs a little push.

helpwithscript.zip

[Singlequanta]

Ahhhh ok;

Well there are several ways to approach this. You could define the relationship to allow creation of records and then deal with it from the original layout or you could use variables and manually create the record.

I have modified your script to do the later so that you can have an idea of how to use variables to accomplish this. My reasoning is that variables are a relatively new feature of FileMaker and they are pretty cool

Let me know if this works for you.

Actually I just realized that I don't remember if FM 7 uses variables or if that was an FM 8 feature. Let me know if this works for you. If not then I will modify the script to either use global fields or automatically create the record from the original layout.

Cheers

Steve

NEW_CF_Feb22b.zip

Edited February 23, 2006 by Guest

[Maria Tique]

WOW, it worked. THANK YOU SO MUCH. WOW. It is incredible. Thanks, this script is really interesting. I will practice it and see how i can implement it. Thanks.

If you have time, do you think you can show me how to use it with global fields. Only if you have time, if not it is ok. I understand you may be busy. Really, thank you for your help.

[Singlequanta]

Sure thing.

Here ya go. I have created a table called "Globals" that contains three global fields.

It does the exact same process as using variables.

Steve

NEW_CF_Feb25.zip

Edited February 24, 2006 by Guest

[Singlequanta]

Finally; there is also the method of using the relationship to create the record.

If you have a look in the Define Databased menu under relationships, and double click on the relationship between Intake and CESD you will see that i have clicked the option to 'allow creation of related records' in fact i have ticked in for both tables which means you could create the related records in either direction i.e. from intake or from cesd.

All that you need to do is test to see if the related record exists and if not then set any field on the related table to any value and the record becomes created automatically and indexes it with the key fields you specified in your relationship.

I don't know what your application is so I wont be so bold to assume that the visit date you are showing is supposed to be the same date that the record is created.

So I have created a field called "Create" in cesd. By simply setting that field to 1 it will automatically create the record an populate the other fields with the date, patient id and visit id.

This is actually the preferred method of creating related records. But having said that any of the methods show will work just fine.

Steve

NEW_CF_Feb25b.zip

Edited February 24, 2006 by Guest

[Maria Tique]

Hello,

This is great but i thought the purpose of creating global variables is so that the value is carried over throughout different tables, even if the value changed.

For example: Intake has patiend id 600, assessment 2, and 01/01/2006.

If the user changed the assessment and date of this patient to be assessment 3 and 11/11/2006 then it does not update CESD with the change??? It actually ends up prompting the user to create a new record but it should not because it should replace the record with the new change. Sorry to be a bother but i have never been confronted with this.

Is there a way to ensure that if an assessment or visit date is changed, it will automatically change in the other table.

[Maria Tique]

Thanks steve for your concern but if a change is made in the Intake form then it does not update in the cesd. Example, if i want to change assess id and or visit date for patient 600 then the changed data will not appear in cesd. As it stands right now, it prompts the user if they want to create a new record versus updating a change. This is my fault, because i thought having a global variable would automatically carry the change over.

[Singlequanta]

Actually yes....

In practice, there is a very different way that one should approach these sorts of relational matters. I have just left home on a quick business trip and won't be back until tomorrow night. When I get home I will create a sample solution that would be far better suited to your application. I will also explain why you should take this approach instead of your current design.

Cheers

[Maria Tique]

Sounds good. i appreciate your effort. It was my mistake assuming the data would change automatically throughout the tables if a user changed it from the Intake form.

I am not sure if this is of interest to you but i will clue you in on the scope of the DB.

This DB is for children who are sick and need various types of medical treatment, ex. cesd is one of the treatments they go through. In order to receive medical treatment, each child needs to fill out an Intake Form to log all their assessments and visitdates. (This logs all children into the DB and all their assessemnt id's and dates). After this occurs, each child will go through different treatments, example cesd treatment. And each treatment consist of 9 assessments and 9 visitdates. Example:

Intake: patient 600, assessment 1, 01/01/06

patient 600, assessment 2, 02/10/06

patient 600, assessment 3, 05/10/06

"" until patient reaches assessement 9

CESD treatment:

patient 600, assessment 1, 01/01/06

patient 600, assessment 2, 02/10/06

patient 600, assessemnt 3, 05/10/06

"" until patient reach assessement 9

PSI treatment:

patient 600, assessment 1, 01/01/06

patient 600, assessment 2, 02/10/06

patient 600, assessemnt 3, 05/10/06

"" until patient reach assessement 9

Notice all treatments occur based on Intake data.

So in other words, each treatment can only occur if the child is logged into the Intake table because this is a log of all their assessments and vistdates that exist. [color:red]The child goes through various treatments all in one day. If a user changes an assessment number or visit date in the Intake table then it should update the cesd table and psi table with the new assessment number and or visit dates.

Note: All treatments are done on the same day the child is logged into Intake. This is why i called Intake the Master Table/Form.

Hope this info helps and aids you in understanding why it is important to update all treatment data (ex. cesd, psi) based on the changes made on Intake. Note, i don't think i added psi table in case you are wondering where it is.

Hope you have or had fun on your business trip and sorry if i wrote too much but i think it may help. Thanks again.

[Maria Tique]

Hi SingleQuanta,

Were you able to figure it out. Perhaps you are still on your business trip or just enjoying the beautiful beach in Australia. Well if you get a chance do you think you can take a look at how to update the records from Intake? Thanks for all your help. MT