Conditional relationships for custom patient rates

[efrank]

Hello - I'm a little stuck editing a relationship structure and wonder if I'm on the right track...

 

I've built an invoicing database for my friend's therapy practice with tables for the following:

 

Patients, Invoices, Appointments, Codes

 

The invoice body is a portal to the "Appointments" table. Each appointment includes a date and procedure code, and the description and rate for that code are lookups in the "Codes" table.

 

I would like certain patients to pay different rates for the same codes. I created a "Custom Codes" table which includes the account number, and made two table entries for it in Relationships: one joining the account number to the "Patients" table, and a second joining the code to the "Appointments" table. The new table appears as a portal on the patient record.

 

I envisioned the following:

 

On a new invoice, I enter the first appointment date and code. The database checks to see if that code has an entry in the "Custom Codes" table for that patient. If so, it uses the rate therein. If not, it uses the standard rate from the "Codes" table.

 

I can't see how to implement this, and suspect my strategy may be flawed. Any suggestions appreciated!

 

Many thanks,

Evan

[Lee Smith]

Why not attach a copy of your file?

 

Here are the basic steps.

How to add an attachment to a reply

[David Jondreau]

You could have one table for codes both standard and custom codes. The standard codes would just have a blank patient ID field. You could have two relationships (or SQL statements) that checks for both a matching code and a matching patient ID field, and if empty, checks for just the matching code.

[efrank]

Hi David - Thank you for your reply.

 

I've added a patient ID to my "Codes" table. It is joined to the "Appointments" table via the "Code" field. In the "Codes" table, I duplicated one of the codes, changed the rate, and assigned a patient ID. How would I define the relationships so the "Rate" field in "Appointments" behaves as you describe?

 

I tried joining the "Codes" table to the "Patients" table via patient ID, but FM gives me the "no more than one relational path" error. Do I need another table occurrence of the "Codes" table?

[efrank]

Aha! Thank you, David; you got me totally on the right track. In case others have this question, here was the solution:

 

I followed David's advice, combining the standard and custom codes in the codes table, and I kept both the one-field and two-field relationships. 

 

I then made two lookup fields, each based on one of the relationships, one for "Custom Rate" and one for "Standard Rate." 

 

Then I turned the "Rate" field lookup into a calculation which uses the custom rate if that field is populated. Otherwise, it uses the standard rate.

 

The final tweak was a script trigger on the code field which, upon entry, clears the custom rate lookup fields. This is in case the user changes from a code that has a custom entry to one that does not.

 

I changed the title of my original post as I found other tips for this problem with that search string...ef

[David Jondreau]

Sounds like you have it covered, but a couple of additional notes....

 

Your relationship graph shows Table Occurrences (TOs) which are representations of tables. You can't have more than one path between any two TOs, but you can add TOs based on the same table. So, yes, the path problem you were having can be resolved by adding a second TO based on the Codes table.

 

You could have two TOs of the Codes table, one based on Patient ID and Code ID, and the other just on Code ID. Then your auto-enter calculation would check the first for a value and, if empty, use the second. It sounds like that's what you did.

 

There's a few other kludgy ways of doing this, but there is one other clean way and that's to use ExecuteSQL() and drop the relationship issue altogether.

 

Glad you worked it out.