Replace with if statement on either of 2 fields empty

[Cabinetman]

What am I missing? I've tried to break the string down to make sure it's correct but I get the default every time....

If it matters both the referenced fields are calculations.

field1 is a date field

field2 is a text field

Modified for viewing:

If (

(

(IsEmpty ( table::field1 ))

or

(IsEmpty ( table::field2 ))

)

; "X"

; ""

)

Thanks......

[Fitch]

Looks ok... those extra parentheses are distracting me:

If( IsEmpty( table::field1 ) or IsEmpty( table::field2 ) ; "X" ; "" )

I might write it this way:

Case(

IsEmpty( table::field1 ) ; "X" ;

IsEmpty( table::field2 ) ; "X"

)

[Cabinetman]

I put in the extra () when it didn't work because I thought maybe it needed to be () or () and then (() or ()) to make it one statement in the if ( () ; result ; default) type format.

I was just progressing through possibilities to get it to work .... I tried the case also.

I tried re-writing it from scratch and still I get "X" in every record.

UGH !!!

SIDE NOTE: I wasn't really thinking about all this.......

Maybe I should rethink what I'm saying and state everything.

First I know I'm not doing everything like I should but this is a quick patch while I redo my entire database...

Actually the 2 fields are calc fields.....

I'm doing an import

Table1 and Table2 are related

New records imported into Table1 may have a matching record in Table2 with info in either shippeddate or canceled

New records in Table1 have 2 calc fields that ref. those fields via relationship (order#) and display data in Table1 if there is a record in Table2

My thought was to replace field Table1::mark with 'X' if the fields were empty and ''" if not.

Find Table1::mark=""

Delete found records

[comment]

Please don't use "Table1 and Table2" to describe your tables - it's very confusing to those of us who are human. Give your tables meaningful names, and indicate which one is the parent (one) and which the child (many).

if there is a record in Table2 ... if the fields were empty .

There is a very big difference between having a related record in another table, and fields being empty in that record. And if the other table is a child table, you can have several related records there - and some of those may have empty fields.

[Cabinetman]

First ....

My apologies.... I thought it would be easier. Thank you for your tolerance!!

Please don't use "Table1 and Table2" to describe your tables - it's very confusing to those of us who are human. Give your tables meaningful names, and indicate which one is the parent (one) and which the child (many).

I have 3 vehicles trying to get repaired at the moment (which su**s) PLUS a new shipment to put into inventory AND fix this little problem. I've got to much going on and am sorry for wasting time by miss stating some things. Until I read it written out I sometimes don't catch what I'm saying.

anyway I started this file wwaaaayyyyy back on FM v3 so please be kind on the "why is this structured like this and it should be ... I know and am working on a new database.

So actually the tables are in 2 different files sssooooooo .....

file = salesnew

table & field = sales::orderid

file = salesold

table & field = salesold::orderid

Relationship:

sales::orderid = salesold::orderid

There is only 1 matching record in file salesold.

salesnew::shippeddate which is calculation

If ( IsEmpty ( salesold::shippeddate ) ; sales::shippeddate ; sales old::shippeddate )

salesnew::canceled which is calculation

If ( IsEmpty ( salesold::canceledorrefunded ) ; "" ; salesold::canceledorrefunded )

Basic Script:

Import Records

Replace Field Contents [sales::new ; Case ( IsEmpty ( salesnew::shippeddate ) ; "N" ; IsEmpty ( salesold::canceledorrefunded ) ; "N" ]

yet every record is "N"

HOPEFULLY I have this correctly stated. I really didn't think it would be this hard to figure out.... SORRY !!!!!

[comment]

Well, if the calculation returns "N", then at least one of the conditions is true. If that's not what you expect, then check your data - or perhaps your logic. Note that IsEmpty ( salesold::canceledorrefunded ) will return true if there is no related record in salesold.

[Cabinetman]

Note that IsEmpty ( salesold::canceledorrefunded ) will return true if there is no related record in salesold.

AAahhhhhh hhhhhhaaaaaa .... my logic.

I would need to import into salesold first in order for that to work.

So I need to make it a test upon something other than null/empty ... such as maybe Length (salesold::canceledorrefunded) > 0

THANKS!

[comment]

You could test for the existence of a related record AND its field being empty:

not IsEmpty ( Related::Matchfield ) and IsEmpty ( Related::Valuefield ) 

[Cabinetman]

BTW - I got it working .... used your last post.

Thanks