List Calculation depending on another field

[rkass068]

Hello,

 

I am having trouble putting a calculation together. I have 2 tables: 1. Main 2. chemotherapy. They are related by key fields:

 

main:pk_main--->chemotherapy::fk_main

 

Chemotherapy has a field called "chemotype" I am able to list all chemo types in a calculation on the main table as:

 

Main: chemolist = list (chemotherapy::chemotype).

 

This works fine and gives me a list of all of the related records to the main but I want to list only a few of the chemo types, specifically the ones that correpesond with another field in the chemotherapy table called "status"

 

I want to list the chemo types with the status as "stopped" only. Not all of the chemo. I do not know how to do this. I tried the filter function, etc but no luck.

 

thanks for the help

[Lee Smith]

Hi rk,

 

I went through Chemotherapy in 2011.

 

I made a small database so that I could track my vitals such as weight, temperature, blood numbers, etc.

 

Your database sounds like something I  would like to help you with, so why not post a copy of what you have so I can see what your needs are?

 

Lee

[bruceR]

Do you really need a list? Or a portal? What's the next step - how do you use the list?

It's easy to add a filter calc to a portal.

[rkass068]

I would like a list. I know how to filter through a portal but I want one with commas (which i know how to do) but I just want the stopped chemo to put on a small summary form we print out for the patient. 

[Fitch]

A no-frills way to do this is to add a calculated field in the chemotherapy table, let's call it chemotypeStopped:

 

Case( status = "stopped" ; chemotype )

 

Now in your Main table your calc would be:

 

List (chemotherapy::chemotypeStopped)

[comment]

I would like a list. I know how to filter through a portal but I want one with commas

 

To filter at the data level (where the calculation engine can access the filtered results), you must filter the relationship itself. More precisely, you need to construct an additional relationship, for example:

 

Patients::PatientID = Treatments 2::PatientID

AND

Patients::gStatus = Treatments 2::Status

 

where Treatments 2 is another occurrence of the Treatments table. The global gStatus field can be formatted as a checkbox, so that you can select multiple values to include in the list.

 

 

Another option is to use a custom recursive function to list the treatment types of selected status(es) - but you would need the Advanced version to implement this.

 

 

I just want the stopped chemo to put on a small summary form we print out for the patient. 

 

 If the status value you need to filter by is a constant, then the suggestion made by Fitch should work well for you.

[Mark Scott]

Hi rk,

 

Another option might be to use the ExecuteSQL ( ) function.  I've attached a quick example that displays a comma-delimited list of a patient's "stopped" chemotherapy courses their record in the main table layout.

 

hth,

 

Mark

Chemotherapy.fmp12.zip

[comment]

Oops, according to the error message, apparently I'm not "permitted to upload this kind of file" ("this kind of file" being a 193KB .fmp12 file). 

 

You must zip the file first.

[Mark Scott]

You must zip the file first.

Thanks, Comment, and . . . done!

[rkass068]

Thanks guys! I will see which one works better. Im sure both solutions are good but I am reluctant to use execute sql in my db because i did use it before in some cases and it really slowed down my filemaker server. Ill test it  out though!