4 Replies Latest reply on Jan 7, 2017 1:16 AM by erolst

    Using relationships to match blank fields

    ndveitch

      Hi There,

       

      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?

        • 1. Re: Using relationships to match blank fields
          erolst

          ndveitch wrote:

           

          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?

          You have to ask the FMI engineers that. Until FMP10, this was possible, but with newer versions, you need a workaround. (I remember that we had to change a solution in which we had relied on that behaviour.)

           

          Why not - just as an example - use a payment status field that starts out with a default value of 0 and is, via an auto-enter calc, set to 1 when you enter a cheque number?

           

          (Re-reading this: it could also be a calc field, if cheque number is in the same table. The important thing is that your "payment status" field can be indexed.).

          1 of 1 people found this helpful
          • 2. Re: Using relationships to match blank fields
            ndveitch

            Thanx for this. I thought as much. It would be nice if they could put that ability back, but they must have had their reasons.

             

            I created the calc field and its working nicely for now

            • 3. Re: Using relationships to match blank fields
              BruceRobertson

              "You have to ask the FMI engineers that. Until FMP10, this was possible..."

               

              Are you sure about that? Examples? AFAIK this has never been possible.

              Though it seems a useless question, it's not possible now at any rate.

              • 4. Re: Using relationships to match blank fields
                erolst

                BruceRobertson wrote:

                Are you sure about that? Examples? AFAIK this has never been possible.

                Though it seems a useless question, it's not possible now at any rate.

                You may want to read my entire post.

                 

                At any rate, if you dig out an old copy of v10 or v9 ...