How to display calculation correctly

[loscamos]

Hello, I have a database with a lot of field with a button that can be sticked/unsticked. I decide to create a calculation field to wrap up all the content sticked/unsticked. For example:

Playstation 1   √
Playstation 2   x
Playstation 3   x
Playstation 4   x
Xbox               √
Xbox 360        √
Xbox One       x

Wrap up field: All console: Playstation 1, Xbox, Xbox 360

This is how I created the calculation field:

if (  isEmpty ( Product::Playstation1 ) ;
"No Playstation 1" ;
"Playstation 1" )

if (  isEmpty ( Product::Playstation2 ) ; 
"No Playstation 2" ; 
"Playstation 2" )

if (  isEmpty ( Product::Playstation3 ) ; 
"No Playstation 3" ; 
"Playstation 3" )

if (  isEmpty ( Product::Playstation4 ) ; 
"No Playstation 4" ; 
"Playstation 4" )

 

The problem is this: when the field is empty the wrap up show only "False" when for example Playstation 1 is sticked it shows "False Playstation 1)

Edited September 21, 2015 by loscamos

[comment]

I have a database with a lot of field

This is not a good approach to begin with. Either use a single field formatted as checkboxes, or records in a related table.

[loscamos]

Ok got it but just for explanation purposes why my calculation does not work?  

[comment]

Ok got it but just for explanation purposes why my calculation does not work?  

That's hard to say when the formula you have posted is obviously not the formula you have used.

[loscamos]

The formula I used is the same, the only things that change are the names.

If ( EVuoto ( IPERTENSIONE ) ; "Nessuna ipertensione" ; IPERTENSIONE )  & 

If ( EVuoto ( CARDIOPATIA ) ; "Nessuna cardiopatia" ; CARDIOPATIA )  & 

If ( EVuoto ( DIABETE ) ; "Nessuna diabete" ; DIABETE )  & 

If ( EVuoto ( DISLIPIDEMIA ) ; "Nessuna dislipidemia" ; DISLIPIDEMIA )

 

 

Even if using If ( EVuoto ( Databse::IPERTENSIONE ) ; "Nessuna ipertensione" ; IPERTENSIONE ) nothing change

Edited September 22, 2015 by loscamos

[Steve Martino]

Couple of things.  I really think you need to follow Comments comment:

This is not a good approach to begin with. Either use a single field formatted as checkboxes, or records in a related table.

As far as your calculation, I would first consider a Case function for multiple parameters.  If you want to test for multiple parameters in a single test you use  AND not &.

If you have a field with one or more checkboxes checked, it will never evaluate true for any isempty test.

 

[comment]

The formula I used is the same, the only things that change are the names.

No, it's not the same. Now you have a & between the If() statements, so the syntax is valid. But it still wouldn't produce a result of "False" under any circumstances.

 

Edited September 22, 2015 by comment

[Steve Martino]

You never really state how you wanted this calculation shown, so see if this is what you are talking about in its simplest form:

 

CheckListSummary.zip

And here's the basic calc:

Let([
  d=Case(IsEmpty(Patient::Diabetes);"No Diabetes";"Diabetes");
  e=Case(IsEmpty(Patient::Dyslipidemia);"No Dyslipidemia";  "Dyslipidemia");
  f=Case(IsEmpty(Patient::Heart_disease); "No Heart Disease"; "Heart Disease");
  g=Case(IsEmpty(Patient::Hypertension); "No Hypertension"; "Hypertension")
  ];
List(d;e;f;g)
)

 

Edited September 22, 2015 by Steve Martino
additional info

[Steve Martino]

Here's another way:

 

CheckListSummary2.zip

[Lee Smith]

Or you can use this as a different way to display to your calc List( Symptoms )

Substitute ( Symptoms ; ¶ ; ", " )

[comment]

OP asked:

... just for explanation purposes why my calculation does not work?  

Posting alternative calculations does not answer the question.

[loscamos]

@Steve Martino Thanks that is was I was looking for. My database has old record and new record. The calculation you used works for new records but not for old ones. Attached you can see the actual database with old and new record. I have 8000 old record and I can not understand why the calculation only works for new record and not for the old one. 

Working:Not Working.fmp12

[Steve Martino]

 A mystery to me.  Your calculations should be set as 'text', not as number. 

I tried recovery, and rebuilding the indexes, but it did not help.

If I make a clone of your database, then import the records from the DB you posted, it works fine.

As a side note, and my Italian is not very good.  The structure of you DB doesn't seem correct.  You appear to have too many fields in the main table, that should be in related tables.  Specifically, the conditions-heart disease, diabetes, etc., Patient Visits & follow ups. It will be very hard to do reports, based on the current structure.

You should have a patients Table, with just pertinent patient information.  Then you can have a Visits table, which can be a tab on the main table, or a portal, showing the patients visits, which you can click on for additional information.  On this record would be all the pertinent information about the visit-reason, diagnosis, medications prescribed, follow up to specialist.  The reason, can be a value list, the diagnosis can also be a value list or separate table, where you can populate the visit record from a dropdown list.

Another thing about your DB, if you check Diabetes, that only tells you the patient now has diabetes, not when it was discovered or diagnosed.

You may want to search the internet to look for a sample DB related to your field, or check out FM Starting Point for some help.

Working_Not Working_Clone.zip

[Lee Smith]

Nice reply Steve.

This link may help them. http://health.usf.edu/doctors/patient-forms.htm

 

[loscamos]

I already created a new database for the patients and I know that the actual one is completely wrong. The only problem is that I need to import the data from the old database to the new one. Since I do not want all that field in the new database I created only one field instead of the checkboxes but I need to import them. That's why in the old database I created a filed named "all desease", so I can import them as a text in the new one.

@Steve Martino Can you try explain again how you made it work? I made a copy of Working/not working and deleted the records and imported again from Working/not working but I still have the issue...

[dwdata]

If it is not important to have the name in the multiple fields, make the fields a number result type, define a value list called ONE, format each of the fields as a checkbox using that ONE value list.

Define this calculation:

Let([
L = 
Product::Playstation1 & "No PlayStation1¶" & 
Product::Playstation2 & "No PlayStation2¶" &
Product::Playstation3 & "No PlayStation3¶" &
Product::Playstation4 & "No PlayStation4"
];
Substitute(L; "1No "; "")
)

Hope this helps!

[loscamos]

@dwdata I tried your calculation but it has errors. I attach again a copy of the database. The problem is that the attached database (which is the old one) must be imported in the new one. The old database (attached) had all the checkboxes, I created a field for all the medicine that will be imported in the new database but for the old record the all medicine field does not display calculation correctly.

 

Db.fmp12

[dwdata]

The reason for this is the OLD records have the value "False" in the field already. Maybe from an import. Since you have it formatted as a CHECKBOX, this is not visible. When you CHECK this box, it put this value in like this:

False¶theValue

this produces the CHECK to display

Anyways, I cloned your fields (and removed checkbox formatting) so you can see the RAW contents of each field. Once you see that everything will make sense.

I, also, add my calculation, but tweaked it a bit since you where using the value instead of a "1" on each the field:

Let([
L = 
not IsEmpty(DATABASE PAZIENTI::IPERTENSIONE) & "No Ipertensione¶" & 
not IsEmpty(DATABASE PAZIENTI::CARDIOPATIA) & "No Cardiopatia¶" &
not IsEmpty(DATABASE PAZIENTI::DIABETE) & "No Diabete¶" &
not IsEmpty(DATABASE PAZIENTI::DISLIPIDEMIA) & "No Dislipidemia¶" & 
not IsEmpty(DATABASE PAZIENTI::NEFROPATIE) & "No Nefropatie¶" &
not IsEmpty(DATABASE PAZIENTI::EPATOPATIE) & "No Epatopatie¶" &
not IsEmpty(DATABASE PAZIENTI::GLAUCOMA) & "No Glaucoma¶" & 
not IsEmpty(DATABASE PAZIENTI::TIREOPATIE) & "No Tireopatie¶" &
not IsEmpty(DATABASE PAZIENTI::GASTROPATIA REFLUSSO) & "No Gastropatia/Reflusso¶" &
not IsEmpty(DATABASE PAZIENTI::ICTUS) & "No Ictus"

];
Substitute(L; ["1No "; ""];["0";""])
)

Hope this helps!

Db2.fmp12