4 Replies Latest reply on Oct 21, 2012 12:48 PM by pilot_john

    how to tell if an editable field based on a value list is empty

    pilot_john

      Hello,

      I am both the user and the developer of my database. I am pretty new to filemaker. I also have one other employee that uses my database. I have tried searching the internet and this forum for the answer to the following question but i have not found it yet.

      I was wondering if there was a script that could tell if a field is empty when that field is based on a value list that is based on both the Customer ID and Customer's Full Name and the field is editable. I tried the isEmpty formula/calculation but it returns empty if there is no customer Id associated with the customer. From time to time, I have walk in customers that I know will be a one time customer and for that reason I do not put them in the cutomer's table. I only add customers that I expect to be repeat customers. When that happens, I still need to create an invoice to record the sale. I always type their name into the field mentioned above but since it really only stores the Id and not the name, it doesn't do well with the isEmpty calculation. But filemaker must store the names somewhere because when I go back through past invoices, those names not associated with a customer Id still show up.

       

      As I re-read my paragraphs above, it comes to me that maybe I shouldn't be doing it this way at all. As the user, I never thought about the effects of typing in a name into a box that was a numbers field. I actually just got use to it and started thinking that it was a text box / text field. It only displays the text from the second field (name), it does not display the first field (customer id). So if there is not a way to test if the field is completely empty and/or if there is a better way to record the names of non-repeat customers, I am completely open to suggestions.

      The reason for wanting to do this is because I don't want to be able to add any line items until a customer has been put in first. The reason for this is that I have a script in the line items that uses the customers name. And that script doesn't work the way I want it to if it doesn't have a customer's name to go with it. I guess another way to do that is to have a new window pop up to enter the customers name each time a new invoice is created. Although if there is a way to do it with out another window poping up, I would rather do it that way.

       

      I use FileMaker Pro 11 at work on a macbook Pro running OS X. This is the computer that has the invoicing database on it. I also have a newer macbook pro running OS X at home that has FileMaker Pro Advanced 11. I some times take a copy of the database home to work on it because of the scripting and debugging tools that FMP Advanced has. Also, if it makes it easier, I can upload a copy of the database as an attachment, but I would prefer to strip it of sensitive information first. Hopefully I can just explain the set up and not have to do that.

       

      Conclusion: I would like to know if there is a way to check the editable box on the invoice layout to see if it is empty of any text even though it is tied to a numbers field. If not then I would like any suggestions or workarounds to this problem.

       

      Thanks.

       

      Message was edited by: pilot_john Attached the FM file. Account name is: Tech Leave password blank This account has full access

        • 1. Re: how to tell if an editable field based on a value list is empty
          comment

          I believe your Invoices table should have two fields: CustomerID (Number) and CustomerName (Text). Then you can either choose an existing customer by selecting their ID, or fill in a name of a walk-in customer.

           

          If you like, you can set CustomerName to lookup the name from the Customers table - thus using the same (text) field for both scenarios.

           

           

          In any case, when a field - any field - is empty, the expression IsEmpty ( Field ) should return true, regardless of the field's type or if it has a value list attached to it or not.

          • 2. Re: how to tell if an editable field based on a value list is empty
            pilot_john

            Ok I'm  sorry about this post, I have posted inaccurate information. That discovery led me to figure out what my problem was. The original script I wrote was:

             

            • If [ Invoices::Bill To Customer ID ]
            • Else
            •      Show Custom Dialog "No Cust Id"
            • End If

            I was thinking that the above script would do the same thing as the below script but I was wrong.

            • If [IsEmpty ( Invoices::Bill To Customer ID )]
            •      Show Custom Dialog "No Cust Id"
            • Else
            • End If

             

            I worte the post thinking that I had used the IsEmpty function, when in fact I had not. I also started thinking that it must have something to do with the way the field / value list was tied to one number field but displayed another text field. Maybe that does have an effect on the first version of the script, I'm not sure, but when I put IsEmpty into it, it all worked!

             

            Any suggestions as to why those two scripts behave differently?

             

            To see where the script is in my attached file, look at the top of the script called ProductNameEntry. I have this script run everytime a new product Id is put into the line items portal.

             

            Thanks Michael for saying: "In any case, when a field - any field - is empty, the expression IsEmpty ( Field ) should return true, regardless ..."

            That statement led me to look into what was going on even more.

            • 3. Re: how to tell if an editable field based on a value list is empty
              comment

              The difference between:

               

              If [ not Invoices::Bill To Customer ID ]

               

              and:

               

              If [ IsEmpty ( Invoices::Bill To Customer ID ) ]

               

              is that when the field contains text with no digits other than 0, the former will return True and the latter False.

               

               

               

              Or, in other words:

               

              If [ Invoices::Bill To Customer ID ]

               

              is the same thing as:

               

              If [ GetAsNumber ( Invoices::Bill To Customer ID ) ≠ 0 ]

              1 of 1 people found this helpful
              • 4. Re: how to tell if an editable field based on a value list is empty
                pilot_john

                Thanks for clearing that up. That makes since now.