2 Replies Latest reply on Sep 9, 2014 4:58 PM by DusanSimovic

    Validate related records so their sum is less or equal to 100%

    DusanSimovic

      Title

      Validate related records so their sum is less or equal to 100%

      Post

      I have a list of records containing sales peoples' names and ownership percentage of the total of 100% of an account. The list of sales people and their ownership percentage is displayed in a portal.

      I am trying to prevent users from entering percentage ownership that will add up to more than 100. Meaning if Joe Blow has 50% of the account and Jane Doe has 25%, the next person added cannot have more than 25%.

      I imagine the best way would be to prompt the user that they are entering a value that, combined with the previous values, adds up to more than 100. Is there a way to do this through field validation?

      Thanks!

        • 1. Re: Validate related records so their sum is less or equal to 100%
          philmodjunk

          It is probably possible to do via field validation, but I think a script will be simpler to set up and provide a more user friendly response that makes it easier and less confusing to correct any such error once discovered.

          Especially since you may need to make several entries in the portal and you only want this validation to kick in when the user finishes with the last portal record. If you need to enter 3 records with percentages of 25%, 50% and 25%, you can only detect an error when the user enters a percent for the last record and the totals don't add up to 100%. Field validation will trip on entering the 25% for the first record since you don't yet have a total of 100%.

          Just brainstorming at the keyboard here, but I think you might want to set up a field where you enter a number and then a script creates the number of portal records specifed in that field. An OnObjectExit script on the portal (It will "trip" with each change of portal row) can check the number of portal records with nonEmpty percentage fields and check the total if that number matches the value in the field. Other triggers can catch the user if they try to exit this field or layout parent record with the data partially entered. Count ( PortalTable::PercentField ) will tell you how many records in the portal have a value in that field. Sum ( PortalTable::percentFIeld) can tell you if they add up correctly.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Validate related records so their sum is less or equal to 100%
            DusanSimovic

            Thanks again, Phil. Due to the way these sales people records are entered, I ended up adding a summary field to the table, and validating on every new sales person entry/update. Seems to work great so far.

            I appreciate the advice!