Case with check 2 fields are 0 or empty

September 16, 201411 yr

I can't get this simple calculation to work.

Field names;

Barcode (item barcode)

Exact Import Gadgets Gewicht (imported weight from sql)

Gewicht (manual entered weight)

Barcode | Exact Import Gadgets Gewicht | Gewicht | RESULT

99 | (any) | (any) | "WEIGHT" (used for csv export)

any but 99 | 0 or empty | 0 or empty | "UNKNOWN"

any but 99 | any | any | Exact Import Gadgets Gewicht

any but 99 | 0 or empty | any | Gewicht

any but 99 | any | 0 or empty | Exact Import Gadgets Gewicht

Could anyone please help me out ?

This is how far I got:

Case
(
Barcode = "000000099";"Weight";

IsEmpty (Exact Import Gadgets Gewicht) = 1 or Exact Import Gadgets Gewicht = 0 and IsEmpty (Gewicht) = 1 or Gewicht = 0;

"UNKNOWN";

IsEmpty (Exact Import Gadgets Gewicht);

Left ( Exact Import Gadgets Gewicht;1 ) = "," or Left ( Exact Import Gadgets Gewicht;1 ) = "0";"0" & Substitute( GetAsText (Round(Exact Import Gadgets Gewicht;2));",";".") & " g"; Substitute( GetAsText (Round(Exact Import Gadgets Gewicht;2));",";".") & " g";

Left (Gewicht;1 ) = "," or Left (Gewicht;1 ) = "0";"0" & Substitute( GetAsText (Round(Gewicht;2));",";".") & " g"; Substitute( GetAsText (Round(Gewicht;2));",";".") & " g"

)

September 16, 201411 yr

Field names;

Barcode (item barcode)

Exact Import Gadgets Gewicht (imported weight from sql)

Gewicht (manual entered weight)

Could you please specify the type of each of these fields?

September 16, 201411 yr

Author

Could you please specify the type of each of these fields?

Barcode NUMBER

Exact Import Gadgets Gewicht NUMBER

Gewicht NUMBER

Result TEXT

September 16, 201411 yr

If I rewrite your "truth table" as follows:

and assume that Exact Import Gadgets Gewicht and Gewicht are Number fields, then =

Case (
Barcode = 99 ; "WEIGHT" ;
Exact Import Gadgets Gewicht ; Exact Import Gadgets Gewicht ;
Gewicht ; Gewicht ;
"UNKNOWN"
)

will satisfy the above conditions.

--

P.S. Please update your profile to reflect your version and OS.

2

September 16, 201411 yr

Author

If I rewrite your "truth table" as follows:

   Barcode     | Exact Import Gadgets Gewicht | Gewicht            | RESULT

1. 99          | any                          | any                | "WEIGHT"

2. not 99      | not (0 or empty)             | any                | Exact Import Gadgets Gewicht

3. not 99      | 0 or empty                   | not (0 or empty)   | Gewicht

4. not 99      | 0 or empty                   | 0 or empty         | "UNKNOWN"

and assume that Exact Import Gadgets Gewicht and Gewicht are Number fields, then =
Case (
Barcode = 99 ; "WEIGHT" ;
Exact Import Gadgets Gewicht ; Exact Import Gadgets Gewicht ;
Gewicht ; Gewicht ;
"UNKNOWN"
)
will satisfy the above conditions.

--

P.S. Please update your profile to reflect your version and OS.

That Simple !? Must say I don't understand / see the logic behind this working (but it does).

September 16, 201411 yr

I don't understand / see the logic behind this

Two points that might help:

1. The Case() function returns the result of the first test that evaluates as True;

2. When used as a test, a Number field will evaluate as False if (and only if) it is 0 or empty.

--

P.S.

"000000099" is not a number.

September 16, 201411 yr

Author

Thank you su much for your help!!!

Final code to make it fool proof and changing the decimal , to .;

Case
(

Barcode = "000000099" ;
"Weight" ;

Exact Import Gadgets Gewicht;
Case
(
Left ( Exact Import Gadgets Gewicht;1 ) = "," or Left ( Exact Import Gadgets Gewicht;1 ) = "0";
"0" & Substitute( GetAsText (Round(Exact Import Gadgets Gewicht;2));",";".")  & " g"; Substitute( GetAsText (Round(Exact Import Gadgets Gewicht;2));",";".") & " g"
);
Gewicht;
Case
(
Left (Gewicht;1 ) = "," or Left (Gewicht;1 ) = "0";
"0" & Substitute( GetAsText (Round(Gewicht;2));",";".")  & " g"; Substitute( GetAsText (Round(Gewicht;2));",";".") & " g"
)
;

""
)

September 16, 201411 yr

That Simple !?

Yes, it is that simple - and will not get any better by making it more complicated. For example:

Left ( Gewicht ; 1 ) = ","

will return true if Gewicht contains a value of ,99999999 (using a decimal comma). OTOH, if you use the same calculation on a system that uses a decimal period, the above will never be true. It will also return false if Gewicht is empty - so this is definitely not what you asked for. It's better to use number functions to test numeric data.