How would I work my relationship to search for blank records? I have a work in progress table that pulls data from various modules in the system via a script and then consolidates it for the user. I am busy creating check fields in my WIP table to see if I can make the process quicker.
I am playing around with relationships and so far I have been able to make check fields for most of the modules because it is fairly simple, where Origin = Name in module and were the unique numbers match. The one issue I have is for unpaid invoices. In my script I go to the Invoices table and search for all records where cheque number is blank. These are the unpaid invoices that need to be on the WIP table. I thought that I could create a blank field in my WIP table and then link that to the cheque number field in Invoices along with the matching unique numbers and origin = Name, as I did with the other modules, so that I could create a count field, but it is not working. If there is data in the blank field and matching data in the cheque number field, for example, 1 in the blank field and 1 in the cheque field, then I get the counter to move up, but if I remove the 1 in the blank field in WIP, it doesnt count up all the records in the Invoices table where the cheque number is blank.
Do i have to create a calc field to say, If ( IsEmpty(chequenumber) ; 1 ; "") and then change my blank field in WIP to rather be a 1? Why would the relationship not match on blank fields?