pc Case statement issu

[Twist6]

Hello all,

I am new to both FMP 13 and developing. 

I operate a not-for-profit travel clinic and wish to automate writing prescriptions with data called from a form that I have developed.

The paediatric dosing depend upon weight of the child, and the total doses dispensed, for the duration of the trip.

I have a Case statement, which in its individual parts works well, but when put together only returns the first part.  I have spent unknown hours looking at this but cannot determine what the issue(s) are.  I would be very thankful for any advice.

Radio buttons were used for “accept” or “decline” by the parent, and calling the prescription (Rx) body, instructions for use, and total doses.

I apologise for any formatting issues with this post , I tried before but the text all ran together, and was difficult to read.  So, I am trying again.

 

 

Case(

PaedMalUptake = "accept" and  (not IsEmpty ( Paed Malarone2 )) ;

GetAsText (Malarone Rx paed)  & ¶ &  GetAsText ( Paed Malarone2 ) &"     MITTE:  "& GetAsText ( Paed Malarone2Total2) ;

PaedMalUptake = "accept" and (not IsEmpty ( Paed Malarone3 )) ;

GetAsText (Malarone Rx paed)  & ¶ &  GetAsText ( Paed Malarone3 ) &"      MITTE:  "& GetAsText (Paed Malarone3Total3) ;

PaedMalUptake = "accept" and (not IsEmpty ( Paed Malarone4 )) ;

GetAsText (Malarone Rx paed)  & ¶ &  GetAsText ( Paed Malarone4 ) &"      MITTE:  "& GetAsText ( Paed Malarone4Total4) ;

PaedMalUptake = "accept" and (not IsEmpty ( Paed Malarone5 )) ;

GetAsText (Malarone Rx adult)  & ¶ &  GetAsText ( Paed Malarone5 ) &"      MITTE:  "& GetAsText ( Malarone Tabs);

" ")

[Brooks]

Not sure what result you are after, but once a case function evaluates true it stops evaluation. If you need each of those conditions to evaluate each one should reside in its own case or if statement.

[Wim Decorte]

It's not clear what the intended output is, but the Case() function will only return one result. One like of text in your case.  It is never going to return more than one if that is what you wanted.

Case() will stop the function when the first True condition is met.

[Twist6]

I did wonder whether that might be the case.  I so appreciate your advice on this and I will try and rewrite. The intended output is a pre written prescription, with the added instructions, and the calculated number of doses.

Thanks again for such quick replies, I have no resources in my area that program in Filemaker Pro so I was stumped.

[eos]

What the guys said.

 

If your goal is to compile a list of entries, depending on the state of your radio buttons, do it like shown below. Note that GetAsText() normally isn't necessary to use numbers in text (as opposed to dates and times).

 

Note also that multiple similarly named fields usually indicate a non-optimal structure, where these fields should be converted to related records.

 

That would make calculations like this one much easier, and more flexible. (E.g. you'd have to amend this calculation and create new total fields if you add more "Paed Malaronen” fields; but if you summarize related records, you simply add new records.) 

Let ( [
  rxpaed = Malarone Rx paed ;
  theString = "      MITTE:  "
 ] ;
 Case (
  PaedMalUptake = "accept" ;
  List (
    Case ( not IsEmpty ( Paed Malarone2 ) ; List ( rxpaed ;  Paed Malarone2 & theString & Paed Malarone2Total2 ) ) ;
    Case ( not IsEmpty ( Paed Malarone3 ) ; List ( rxpaed ;  Paed Malarone3 & theString & Paed Malarone3Total3 ) ) ;
    Case ( not IsEmpty ( Paed Malarone4 ) ; List ( rxpaed  ; Paed Malarone4 & theString & Paed Malarone4Total4 ) ) ;
    Case (not IsEmpty ( Paed Malarone5 ) ; List ( Malarone Rx adult ; Paed Malarone5 & theString & Malarone Tabs ) )
    ) 
  )
)

[LaRetta]

I would also mention that, whenever you see fields with 1, 2, 3 after them it almost-always indicates improper structure and those fields should be records in a related table.  I believe the duplicity (thus complexity) that you are seeing in your calculation is just one symptom of it, as Eos indicated.  If you wish, you can zip and attach your file for a quick review.

 

And welcome to FMForums, Twist!!

[Twist6]

You are all most kind and helpful. Thank you Eos for taking out your valuable time to assist Wim and Brooks, and also LaRetta for your sage advice.

This is my first venture into producing a database, and as I have proceeded I recognize that I need to address my nomenclature to avoid such complexities in the future.

I will try Eos' solution, and zip the file if I am still unsuccessful. I have other medications that I need to use a similar structure, and I believe that if I can solve this one, I should be able to adapt the others.

I'll return shortly. Thanks again everyone.

Twist

[Twist6]

Hello all,

 

Eos, I have inserted your recommendations, and below are the results.

 

You can see that the correct prescriptions and dosages are displaying (in blue) for two different weights of children, but the other prescriptions are also not being suppressed so are appearing as well.

Because there are four dosages possible, they are also appearing in the same order as the script (i.e.down the page 1-4).  I could probably live with the displacement, but do you have any ideas why the other prescriptions are being called?

Thanks again for your input.

Twist

 

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

1 paed. tablet/day      MITTE:  24

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

       MITTE:  

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

       MITTE:   

MALARONE tabs                         

 

Sig:  1 tablet daily with food, starting 1 day prior to entering the malarial area, daily while there, and for seven days after departing the malarial area, at the same time each day.

Not to be taken if pregnant or breast feeding

MITTE:

       MITTE:  24

 

 

 

   

 

 

 

 

 

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

       MITTE:  

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

2 paed. tablet/day      MITTE:  48

 

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

 

       MITTE:  

MALARONE tabs                         

 

Sig:  1 tablet daily with food, starting 1 day prior to entering the malarial area, daily while there, and for seven days after departing the malarial area, at the same time each day.

Not to be taken if pregnant or breast feeding

MITTE:

       MITTE:  24

[Twist6]

Sorry,

Forgot to mention that the above reflects the coding that Eos provided viz:

Twist6

Let ( [

rxpaed = Malarone Rx paed ;

theString = " MITTE: "

] ;

Case (

PaedMalUptake = "accept" ;

List (

Case ( not IsEmpty ( Paed Malarone2 ) ; List ( rxpaed ; Paed Malarone2 & theString & Paed Malarone2Total2 ) ) ;

Case ( not IsEmpty ( Paed Malarone3 ) ; List ( rxpaed ; Paed Malarone3 & theString & Paed Malarone3Total3 ) ) ;

Case ( not IsEmpty ( Paed Malarone4 ) ; List ( rxpaed ; Paed Malarone4 & theString & Paed Malarone4Total4 ) ) ;

Case (not IsEmpty ( Paed Malarone5 ) ; List ( Malarone Rx adult ; Paed Malarone5 & theString & Malarone Tabs ) )

)

)

)

[Wim Decorte]

For the example you give, can you tell us what is in all the fields?  That way we can tweak the calc.

The other lines would show up if there are values in Paed malarone3 through 5

 

 

Fields that I see in the calc:

 

Malarone Rx paed

rxpaed

Paed malarone2

Paed malarone3

Paed malarone4

Paed malarone5

Paed Malarone2Total2

Paed Malarone2Total3

Paed Malarone2Total4

Malarone Rx adult

Malarone Tabs

[eos]

Seeing your sample output, I assume that Paed Malarone2, 3 etc. are the fields that hold your standard texts to use in the prescriptions; and since those are never empty, you always get a complete list as calculation result.

 

IMO, what you actually need is a field to indicate the weights - e.g. PaedType, with a value list – and use the contents of that field in the calculation. **

 

But we need more info to be sure …

 

EDIT: See attachment for an example of **

PrescriptionsList_eos.fmp12.zip

[Twist6]

Hello Wim, Eos and all,

The called fieds should be blank unless they fit the weight parameters, so I do not understand why they print.  Thanks for you continued support

Twist6

Paed Malarone2

If(((ChildWtKg)  ≥ 11) and ((ChildWtKg) ≤ 20);"1 paed. tablet/day";" ")

Paed Malarone3

If(((ChildWtKg)  ≥ 21) and ((ChildWtKg) ≤ 30);"2 paed. tablet/day";" ")

Paed Malarone 4

If(((ChildWtKg)  ≥ 31) and ((ChildWtKg)<40);"3 paed. tablet/day";" ")

Paed Malarone5

If(((ChildWtKg)  ≥ 41);"1 adult tablet/day";" ")

 

Paed Malarone2Total2

If((Paed Malarone2) = " ";" "; Round ( ( ( Days )+8 ) + .499999 ; 0 ))

Paed Malarone 3Total3

If((Paed Malarone3) = " ";" "; Round ( (((Days)+8)*2) + .499999 ; 0 ))

Paed Malarone4Total4

If((Paed Malarone4) = " ";" "; Round ( (((Days)+8)*3) + .499999 ; 0 ))

 

Malarone Tabs

Round ( ((Days)+8) + .499999 ; 0 )

 

Malarone Rx paed  (Also = rxpaed , from ‘Let’ statement)

Is just a text field containing the prescription instructions ie

MALARONE Paed. tabs (62.5 atovaquone/ 25mg proguanil)

 

Sig:  Begin 1–2 days before travel to malarious areas. Take daily at the same time each day while in the malarious area and for 7 days after leaving such areas.

 

Contraindicated in people with severe renal impairment.

Should be taken with food or a milky drink.

[Wim Decorte]

Hello Wim, Eos and all,

The called fieds should be blank unless they fit the weight parameters, so I do not understand why they print.  Thanks for you continued support

 

If(((ChildWtKg)  ≥ 11) and ((ChildWtKg) ≤ 20);"1 paed. tablet/day";" ")

 

 

They are not empty; you're putting a space in them...

[LaRetta]

Paed Malarone2

If(((ChildWtKg)  ≥ 11) and ((ChildWtKg) ≤ 20);"1 paed. tablet/day";" ")

 

Also, too ... please remove the unnecessary parentheses ... they just add clutter and complexity.  It can be written as:

 

If ( ChildWtKg  ≥ 11 and ChildWtKg ≤ 20 ; "1 paed. tablet/day")

 

If you want the result if NOT true to be nothing then no default result is required.  "1 aped. table/day" should NOT be hard-coded in a calculation but should be data in a record.  As is, you will need to open schema just to change a value and that is not good method.  Really, since you are early in your project, now is the time to step back and consider structuring this properly.

 

If you get the base right, the rest just falls into place.  As is, you will need Total1, Total2, Total3 and then eventually Summary1, summary2, summary3 and so on ...

 

Your solution (as is) requires (at minimum) three-times more work than needed.  Truly.  Truly.  

I should add that, if new to relational design and databases, the BEST use os money spent is on the base structure.  With correct structure, the rest is much easier.  With wrong structure, it is a mess to correct later.  So spend more time deciding on structure - post your sample structure file here to let us help you.

 

And then ONLY once you have a good base in place should you begin to build the file and add the goodies.  Folks get in too much of a rush and then they are stuck with an underlying structure which does not work.  It is very sad.  We see it repeated over and over.

[Twist6]

Thank you Eos for fowarding your file.  I will have a look at it when I get home tonight after work.

Thank you too Wim for pointing out the 'space'.  I will make the amendments. 

That was stupid of me, but I will learn from the error, and also your counsel.  I do appreciate all of your patience with beginners such as myself, and your willingness to share your accumulated knowledge.

Twist6

[Twist6]

It seems that I have a lot of cleaning up to do with this solution, so I will do some of that.  Your points LaRetta are helpful.

 

Eos, your 'Let' statement above worked flawlessly, and I so appreciate you guidance and the time you devoted to writing this for me, thank you.

 

Wim, your observations and feedback were also pivotal to getting this to work.  Thanks also to Brooks.

 

As I have mentioned before this is my first venture into any forum and I am most impressed to find so many 'helping hands'

 

I'm going to clean the language up a bit.  I believe you have all contributed to my being able now to successfully complete this project.  Thanks a lot.

 

Twist6   

[Twist6]

eos,

Many thanks for the file you sent.  It's a little difficult for me to explain how this form works without showing an example.  Would you be willing to have a look if I zipped it and attached here.  If that is against protocol, I apologise ahead of time, and certainly understand if you are unable to do this.  You have been most helpful already.

Twist6

[Lee Smith]

Attach your file following the steps shown here.

 

How to add an attachment to a reply

[Twist6]

Hello everyone,

I have uploaded my travel clinic form to make things a little clearer.

Allow me to prefix my question by reiterating that this is my first foray into using FileMaker pro 13 and indeed my first effort at programming, so I am sure that the coding is pretty “dirty”, as my only knowledge this coming from books and a few online tutorials.

That having been said here is the issue that I’m trying to solve. I have calculated fields for both adult and paediatric prescriptions, as well as the total number of doses required for their trip, calculated from the number of days of their visit.

For malaria prophylaxis I am using a layout which calls for prescription instructions, the type of medication that has been decided upon (drop down menu). The decision is made from the “accept” or “decline” radio buttons. As you can see, although very rough this seems to be working okay.

When it comes to paediatric dosing, this is more difficult because of the variation in dosing required by weight.

What I really want to accomplish, is to have the paediatric prescriptions act in a similar way to the adult prescriptions (in that they occupy the same area on the printed prescription, instead of appearing in a serial way down the page)

Eos was kind enough to suggest a solution for calculating paediatric dosages which I have incorporated and is working well.

As always, your expertise and skill is greatly appreciated. 

LaRetta, your suggestions for simplifying record names is appreciated and I will, when I get time, try to go back and simplify everything as you suggested.

Thank you.

Twist6

TravelSoftware Copy.zip

[LaRetta]

Hey Twist, I'm not ignoring you - I've been busy.  But I also cannot assist you down a dead-end path.  Your diseases should not be fields.  If you do not change it now, you will have to change it later and, at that time, it will be a major undertaking.  

 

I wish you the best with this project, truly.  

[Wim Decorte]

I agree with L.  Prescriptions and Doses should not be calculated fields somewhere.  They are entities by themselves and should be set accordingly in their own tables.  I'm sure we can give you a calculation that will work but it will do you no good in the long run.  In fact it will hold you back from a solid database design.

 

I understand that this is your first foray into solution design, but that should be more reason to do the right thing and not paint yourself into a corner.  Go down the right path and we'll be there for you.

[eos]

Hi Twist,

 

I agree fully with L. and  W. There's only so much that you can do with calculations and scripting if the basic structure isn't sound. 

 

Having said that, I can understand that it's difficult for you to move on when all you hear is “don't go down that path, do it differently”, and you have no clear idea of how to implement a record-based vs a field-based approach.

 

So, to give you a glimpse and a foundation you could continue on, here is a copy of your sample file, with Prescriptions and Dosages as their own entities, and two join tables that connect copies of those “template records” to travel record. One could probably even use a single join table for both prescriptions and dosages (and depending on your print and export needs, should do so). Note the script that generates the related records; you would tie this to the creation of a new Travel record (and subsequently make sure that you don't add them again …)

 

The entries from your numerous “stats” have been put into a table and are used in a conditional value list that connects a travel description entry to its appropriate “stats” values via a(nother) join table. You can do the same with the values from the other type of value lists. While maybe not strictly necessary, this allows you to change the wording very easily, since there is (again) a single reference storage place for a value, and the association to a prescription is via a key, not a text; you could even even (if desired) generate statistics based on the selections.

 

Note that by not burying the entity names and their attributes within fields, sub-fields and value lists, but putting them into records and defining them as (sort of) objects, you can simply go to a table and see a list of existing <entity objects>, create new objects, and define/edit attributes. E.g. each text from the above calculation would now – at least in its basic form – be an attribute of one Malapaed record: out in the open, easily editable and accessible, stored in one place.

 

Also note how easy it is to simply define two fields to keep an internal name and one you want to display on your layout.

 

You can see how all this works by going to the “Main Record Copy” layout. The visual appearance is almost unchanged, but if you look under the hood, you'll see that most parts are now records/portal based.

 

Check out the “Summary NEW” field, and you see how much easier that calculation now is (though, IIUC, you still need to perform calculations for the Malaria entries; but that would be much easier now). The same goes for using only entries with certain values (e.g. accepted ones) in a print layout, or by using only adult or children prescriptions.

 

Be sure to inspect the new tables, and how they are connected to the existing Travel table and to each other.

 

On a side note: if several fields use the same value list, you only need to define that value list once. So one “Accept¶Decline” VL would have worked for all 19 fields … but now it's superfluous anyway.

TravelSoftware_eosMOD.fmp12.zip

[LaRetta]

Just as an aside, I did message Twist that I would be willing to provide a sample file of suggested structure IF they wanted one after I finished the project I'm pressed to complete (which should be another day).  I have not yet received a response.  I don't want folks to think we just tell folks they are structured improperly without support although both Wim and myself made it pretty clear, I think.

 

But it is also the person's responsibility to say, "Okay, yes, I want to change it" otherwise we appear to be pushing something they do not want.

 

Thanks for taking the time to provide the file, Eos.  Even if Twist is unable to change his file at present, this file can help others who recognise they are in the same boat.

[comment]

W is taken, e.

*

[LaRetta]

LOL

[eos]

Even if Twist is unable to change his file at present, this file can help others who recognise they are in the same boat.

 

If they happen to come upon it, in the depths of a thread called “Case Statement Issue” …

 

W is taken, e.

*

 

LOL

 

OK, I bite: what's the joke?

[LaRetta]

I can only tell you why *I* laughed... e

 

Wim started it - he calls me L.  So you responded with L and W.  Then M (or C)  was saying W is already taken by George W. Bush.  And I thought ... wasn't there a singer or someone named L?  So I laughed that we were going down the path of first-letter only.:-)

 

L

If they happen to come upon it, in the depths of a thread called “Case Statement Issue” …

 

It does not matter if a newbie is searching for whether their relationship is incorrect.  In fact, that would be the last thing they would be searching for, but they might hit upon this thread and realise they are in same boat.  We do not know who all reads these threads or when nor do we know their circumstance.  That is why every thread should speak the truth as best we can - or provide follow-up correction if we make mistakes otherwise it can hurt someone down the road.

[eos]

I can only tell you why *I* laughed... e

Wim started it - he calls me L.  So you responded with L and W.  Then M (or C)  was saying W is already taken by George W. Bush.  And I thought ... wasn't there a singer or someone named L?  So I laughed that we were going down the path of first-letter only.:-)

L

 

Right … I should have looked one post higher! Thank's for clearing that up, L, and to C (M) for being cryptic enough to remind me of S.D. in his heydays …

[LaRetta]

I miss Soren!

[Twist6]

Eos,

Thank you for your file, I can see how efficiently it works.  I truly appreciate you help thanks again

Twist6

[Twist6]

Eos,

 

I have just seen your last file example, and I totally (well maybe not 'totally - yet) understand now the use of tables vs fields.  Your comments are also much appreciated, and your sensitivity to the 'newbies' like me that are confused by some of the terminology of a new world.

 

I am very aware of this misunderstanding, knowing that we in the medical world are guilty of thinking that everyone knows what we are talking about, as our language is every-day for us but certainly not for the people we interface with.  This assumption causes most of the issues between Drs and their patients.  So I thank you for recognising this.  The other similarity is that we do not wish to appear stupid by asking dumb questions.

 

Your latest file will allow me to start from 'scratch' using it as a guide, and produce a much better and efficient product. 

 

I am aware of your precious time that you spent in formulating this template .... you are a thoughtful teacher and mentor.  I will strive to be the same.

 

I may have to get back in a few weeks to ask a few more questions and maybe report progress, but promise not to be a burden.

 

Thanks again,

 

Twist6