1 Reply Latest reply on Mar 30, 2012 9:27 AM by philmodjunk

    Checking alternate fields when entering new data



      Checking alternate fields when entering new data




      I am trying to create a script to perform a comparison when entering new data.  Here is my situation:

      There is a field for Client name and there is a second field for Unpaid Invoice.

      I would like it if, when entering a client's name into a new record, FileMaker would display a warning if the entered name has unpaid invoices in any other record.  We have a separate client database, and the Unpaid Invoice is in the form of a Yes/No radio button set. 


      I have a script that currently brings up all of the client's records, but I have to switch layouts to quickly view the past invoices and gauge the status of their payment.  It also makes entering new clients (with no previous invoices) a hassle.

      What would be a more efficient way to achieve this?



        • 1. Re: Checking alternate fields when entering new data

          Is this the current relationship between invoices and clients?

          Clients::ClientID = Invoices::ClientID

          Assuming that it is, in Manage | Database | relationships, make a new table occurrence of Invoices by clicking it and then clicking the duplicate button (2 green plus signs). You can doubleclick the new occurrence box to get a dialog to appear where you can rename the new occurrence box to InvoicesUnpaid.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add a calculation field, constNo, with "No" as it's sole term and select "text" as its return type. (I'm assuming "no" is the value entered into the radio button field when an invoice is unpaid.)

          Now you can link this new occurrence of Invoices into your existing relationships like this:

          Invoices::clientID = InvoicesUnpaid::ClientID AND
          Invoices::constNo = InvoicesUnpaid::radiobuttonField

          You can use this relationship to quickly check for unpaid invoices. One method is to add this text to your layout: "Client has Unpaid Invoices". Then use this conditional format expression: IsEmpty ( InvoicesUnpaid::clientID ) to change the font size of this message to 500 points. This makes the text disappear if there are no related records in InvoicesUnpaid. Now, each time you create a new invoice and select a client for that invoice, the message will appear automatically on your layout if the client has outstanding, unpaid invoices.