Jump to content

Case with check 2 fields are 0 or empty


caphex
 Share

This topic is 2511 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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"

)

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

""
)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2511 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.