Calculation by using unrelated table records

[dbriegas]

(sorry for repeating the entry; i didn't know where to post it!I

Good afternoon!

I'm having some trouble with something I'm trying to do in Filemaker.

I need to perform a calculation by using an auxiliar table, which is unrelated to the one in which I want the calculation field to be. That table shows the available doses in miligrams and the cost associated to each one (cost is not proportional to the dose). The problem is that I don’t know how to retrieve the cost field depending on the dose we choose; let me explain: if the doctor prescribes the 75 mg dose, I want to perform the calculation by using the proper cost (177.6237$) from the auxiliar table. I’ve tried some scripts, but I don’t know how to get to the field that belongs to the record with the dose chosen. Also, I can't do it by using a simple "If" calculation (If proposed_dose = 75; 177,6237 ; If...) because costs change with time and we prefer not to modify field properties (too complex for final users). Any idea? In Excel this seems to be so easy (just need to refer the proper cell from the proper book when writing the calculation), but not that easy in Filemaker (at least for me…).

Thanks in advance!

Daniel (Spain)

 

··· this is the auxiliar table, for the case you need it as a reference:

DOSE_MG	COST_PER_DOSE
75	177,6237
150	319,7111
225	497,3348
300	639,4222
375	817,0459
450	959,1333
525	1136,757
600	1278,8444

Edited July 12, 2018 by dbriegas

[Lee Smith]

Please do not double post you questions.

I have remove the question posted to Manage Scripts.

[Lee Smith]

Hi Daniel, and welcome to the FM Forums,

If I'm understanding you correctly, 

1 hour ago, dbriegas said:

using an auxiliar table, which is unrelated to the one in which I want the calculation

In order to access the information table to table in native FileMaker, it requires a relationship.

Are these tables in the same file or separate files?

Lee

 

 

 

[dbriegas]

2 hours ago, Lee Smith said:

Hi Daniel, and welcome to the FM Forums,

If I'm understanding you correctly, 

In order to access the information table to table in native FileMaker, it requires a relationship.

Are these tables in the same file or separate files?

Lee

 

 

 

Hi again, Lee

Once again, thanks for your help! I attach a couple of screenshots of the database I'm building for my job; as you can read in my profile I'm about a rookie when it comes to filemaker. I've made some other databases, more complex than this one, but I'm stuck here with the problem we're trying to solve. Well, the information table is the little one to the right (its records are shown in the other caption), and the native the other in red. I've related both of them as you see, but I don't know if this is useful or not. I created the auxiliar table in order to be able to modify the information if drug costs change (we expect it to become cheaper with time) without needing to modify a calculation in the future (I'm the only one of my parters who know something about database creation in filemaker). Both tables are, as shown, in the same file.

Let me know if you (or anybody else) need more information!

Good night from Spain!

[Steve Martino]

I would say you could treat it like an invoice.  Add a join table (LineItems).  Make LineItems a Prescription. 

Patient-------<Invoice>-------<Prescription>--------Product

Then a Patient layout can have a portal of all the Invoices.

The cost can then be looked up from product or a calculation.

Here's an old sample file

u/n: admin

p/w: 1234

Navigate to:

-Products, to see Product info, and to change prices.

-Customers, to see customer info and related Invoices.

-Invoices, to see how to create an invoice, picking from a portal.

I didn't have time to strip out everything not needed, but maybe when I have some time.

This should give you the gist of the relationships, scripts and process.

InvoiceSM.fmp12

Edited July 12, 2018 by Steve Martino