Search and copy fields from 1 table to another based on >1 search criterias

Okay so I'm pretty new to all of this. So any help or advice you can give me would be much appreciated.

Basically I'm working on a medical database.

I have multiple tables for various things:

- patient list

- ur numbers

- med records

- imaging records

A patient can have any number of UR numbers, depending on how many hospitals they attend. Generally speaking, there is only ever 1 UR number per patient per hospital.

So UR numbers table has ur_id which is related to id in patient records (which is related to records_id and imaging_id), ur_hospital and ur_number.

I've set up med records and imaging records tables as task flow sheets, with various steps about getting data, which you can fill in a date or text when that step is complete.

However, I want to auto add two hospitals which are regular, and have it look for any available UR numbers for that patient, at that hospital.

So far I have a script that can add these two hospitals to the various lists, and if a UR number exists, it will add it (I don't know why), but if I run this script a 2nd time, the hospitals get added again (duplicates), but not the UR number.

I also have a script that will search and apply the UR numbers for these hospitals like and "update" function if the UR number is added at a later date.

Ideally I'd love a script that could:

1) Look at the taskflow lists (med reocrds, imaging records) and look to see if any UR number exists for the person at that hospital and add it.

2) Automatically add a hospital to the task flow lists if it appears in the UR list

3) Add the basic two hospitals if they do not exist currently

4) If all locations exist in the taskflow sheets, just update the UR numbers.

I know this is a lot of information, so thankyou in advance.

Here's a copy of the scipts:

---Add RCH/MCH to workflow tasks---





Set Error Capture [ On ]

Go to Layout [ “Form (Registry)” (registry_list) ]

Go to Field [ registry_list::id ]

Set Variable [ $id; Value:registry_list::id ]

Go to Layout [ “workflow_sheet_records” (workflow_records) ]

New Record/Request

Go to Field [ workflow_records::records_id ]

Set Field [ $id ]

Go to Next Field

Insert Text [ “RCH” ]

[ Select ]

New Record/Request

Go to Field [ workflow_records::records_id ]

Set Field [ $id ]

Go to Next Field

Insert Text [ “MCH” ]

[ Select ]

Go to Next Field

Go to Layout [ “workflow_sheet_imaging” (workflow_imaging) ]

New Record/Request

Set Field [ $id ]

Go to Next Field

Insert Text [ “RCH” ]

[ Select ]

New Record/Request

Set Field [ $id ]

Go to Next Field

Insert Text [ “MCH” ]

[ Select ]

Go to Layout [ “Form (Registry)” (registry_list) ]

Go to Object [ Object Name: "Med_Records" ]

Perform Script [ “Apply RCH & MCH UR numbers to workflowtask_records” ]

Perform Script [ “Apply RCH & MCH UR numbers to workflowtask_imaging” ]





---Update--- same for the other hospital, just values are changed



Set Error Capture [ On ]

Go to Layout [ “Form (Registry)” (registry_list) ]

Go to Field [ registry_list::id ]

Set Variable [ $id; Value:registry_list::id ]

Go to Layout [ “UR_number_list” (ur_numbers) ]

Enter Find Mode [ ]

Go to Field [ ur_numbers::ur_id ]

Set Field [ ur_numbers::ur_id; $id ]

Perform Find [ ]

If [ Get ( FoundCount ) > 0 ]

Constrain Found Set [ Specified Find Requests: Find Records; Criteria: ur_numbers::ur_hospital: “"RCH"” ]

[ Restore ]

Set Variable [ $UR_RCH; Value:ur_numbers::ur_number ]

#---

#---

Enter Find Mode [ ]

Go to Field [ ur_numbers::ur_id ]

Set Field [ $id ]

Perform Find [ ]

Constrain Found Set [ Specified Find Requests: Find Records; Criteria: ur_numbers::ur_hospital: “"MCH"” ]

[ Restore ]

Set Variable [ $UR_MCH; Value:ur_numbers::ur_number ]

#---

#---

#---

Go to Layout [ “workflow_sheet_records” (workflow_records) ]

Enter Find Mode [ ]

Go to Field [ workflow_records::records_id ]

Set Field [ $id ]

Go to Next Field

Set Field [ "RCH" ]

Perform Find [ ]

Go to Field [ workflow_records::records_ur ]

Set Field [ $UR_RCH ]

#---

Enter Find Mode [ ]

Go to Field [ workflow_records::records_id ]

Set Field [ $id ]

Go to Next Field

Set Field [ "MCH" ]

Perform Find [ ]

Go to Field [ workflow_records::records_ur ]

Set Field [ $UR_MCH ]

Show All Records

Go to Layout [ original layout ]

Go to Object [ Object Name: "Med_Records" ]

Else

Show All Records

Go to Layout [ “Form (Registry)” (registry_list) ]

Go to Object [ Object Name: "Med_Records" ]

End If

 

I guess where I'm coming unstuck on, is how to get it to search for items, such as hospital names, instead of me listing them. As these will be different for every person. I would love it to be dynamic.

Cheers,

fmphysio