Case with check 2 fields are 0 or empty

[caphex]

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"

)

[comment]

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?

[caphex]

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

 

Barcode NUMBER

Exact Import Gadgets Gewicht NUMBER

Gewicht NUMBER

Result TEXT

[comment]

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.

[caphex]

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).

[comment]

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.

[caphex]

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"
)
;

""
)

[comment]

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.