7 Replies Latest reply on May 12, 2016 1:45 PM by flyzone

    Validating a field

    flyzone

      Hello All,

       

      I have an inventory with Items in Stock and Items Sold.

      I created a field called Items Remaining that is validated by this calculation "Items in Stock - Items Sold"

      The system works fine but I would like to put a condition that when you insert new "Items Sold" and the number is bigger than the "Items Remaining" you will get an alert saying you're overbooking.

       

      Now "Items in Stock", "Items Sold" and "Remaining Items" are in the same Inventory table

       

      Entering new Items Sold is on a Sales table and the 2 table are unrelated.

       

      Any suggestions?

       

      Thank you

        • 1. Re: Validating a field
          Mike_Mitchell

          Probably best to use a script for this. Trigger it using either OnObjectSave or OnRecordCommit (depending on your workflow).

           

          As an aside, it's usually better to use scripts to set quantities like this rather than using a calculation field. It avoids FileMaker recalculating every time one of the predecessor fields changes, which helps with performance when you start to get large numbers of records.

          • 2. Re: Validating a field
            flyzone

            Thank you Mike,

             

            You got me lost now   I'm not very familiar with Scripts.

            The input for the New Sales Items is done thru a portal and doesn't require a script for a new record.

            That's why I was thinking it would be easier to just add condition.

             

            The inventory change every month (new items) so the calculations are sometime 1 x item to a max of 10.

            • 3. Re: Validating a field
              Mike_Mitchell

              flyzone wrote:

               

              You got me lost now I'm not very familiar with Scripts.

               

              Sounds like an excellent opportunity to learn something new.  

               

              Seriously, you'll need to learn scripting if you're going to spend any amount of time working in FileMaker. That said,

               

              That's why I was thinking it would be easier to just add condition.

               

              If you want to perform a field validation, you can use that option in File > Manage > Database. Choose the field where new entry is done and click Options. Select the Validation tab. Enter a calculation that has a Boolean (true or false) result. In your example, that calculation might be:

               

                   Items Sold <= Items Remaining

               

              If that is true, then nothing happens. If it's false, then FileMaker will indicate that the validation has failed. You can check the "Allow user to override" checkbox so it will ask if he wants to allow the illegal value to remain.

               

              I'm not 100% certain on your data model here; you say the two tables are not related. You won't be able to validate directly unless you link them together (via, perhaps, a product ID field).

               

              HTH

               

              Mike

              • 4. Re: Validating a field
                flyzone

                That's in the bucket list

                 

                I'm not a FM developer but I like to build solutions in FM for company needs.

                 

                I was browsing around and got to find  some sample of a script and modified it to fit my environment but the script doesn't get triggered. Maybe I'm setting the wrong ID

                 

                 

                Screen Shot 2016-05-12 at 4.16.29 PM.png

                • 5. Re: Validating a field
                  flyzone

                  Never mind I did it ... i forgot to activate the script trigger eheheh

                  Thank you for your help

                  • 6. Re: Validating a field
                    erolst

                    flyzone wrote:

                     

                    I was browsing around and got to find some sample of a script and modified it

                    That screenshot looks eerily familiar ...

                    • 7. Re: Validating a field
                      flyzone

                      If you created, let me tell you, it works like a charm