6 Replies Latest reply on Aug 6, 2015 4:52 PM by philmodjunk

    Validates values when entering records in a portal



      Validates values when entering records in a portal


      Is it there an easy way to validate that the record we are creating in a portal has a unique value for that portal?

      I mean, I can allow having the same value for a record is the portal is in two different parent records, but not in the same.

      Let's say I have two tables called: Jobs and Welds. There is a Job called "A" and another called "B". I should be able to have a weld called "w1" in both Jobs ("A" and "B") but not two welds called "w1" in the same Job.

      I already created a script (triggered when entering values ) that storage in variables the values for the weld and the job and go to the Weld layout and find those record that have the same value and if there is already a record with that value in the same Job I won't allow the user to create it. But I am wondering if there is a faster and easy way to do the same?. I feel that this script will turn slow the DB when having hundreds of thousands of records.

      I will attach the screenshot for you to understand what I am talking about. I just want to create a better DB.

      Thank you so much!



        • 1. Re: Validates values when entering records in a portal

          Here is the script I have created just if someone want to check it.

          • 2. Re: Validates values when entering records in a portal

            If you define an auto-entered calculation on a text field added to the Portal table with this expression:

            ParentTableID & "|" & Weld

            You can then specify a unique values validation on this field and it will "catch" all cases where you have entered the same value twice in the same portal. But this allows the user to make the mistake and then makes them correct it.

            It would be better to design your interface so that they cannot select the same value twice for the same set of related records.

            There are two ways that can be used for this. The simplest is a special type of conditional value list called a "dwindling" or "diminishing" value list. It starts with a list of all possible values, but each time that you select a value, that value then disappears from the value list so that it cannot be selected again in the current context.

            The other method uses a different portal that matches to a table with all possible values for your Weld field. A button in the portal row looks and acts like a check box, but clicking the button either adds or removes a record from your original portal. Since clicking the button a second time removes the record, it's impossible to select the same value twice for a given parent record.

            For a dwindling value list example, see:

            "Adventures in FileMaking #1 - conditional value lists"

            For a "check box portal" you can see:

            "Adventures in FileMaking #2 - Enhanced Value Selection"

            but it doesn't demonstrate a version where the mouse click creates/deletes records so you'd have to study the example and figure out how to modify the script used in the example to do this.

            • 3. Re: Validates values when entering records in a portal

              Hi Phil, first, thank you so much for all your help.

              The "dwindling value list" and the "check box portal" will not work for me in this case. The quantity of welds per project is not fixed and it can change everyday. And also, there can be thousand of well per projects, it won't be practical for the user to manage that amount of record in a list or portal.

              The first option you gave me (ParentTableID & "|" & Weld) make more sense to me. That way I would attach the "ParentTableID" to every "weld number" and make it unique among different "jobs". Nice idea!. Now, I am just wondering how can I make the weld portion of that expression an auto-incremental number.

              Let's say the ParentTableID is "A" so the first weld shall be "A|1" and the second one "A|2" and so on.

              And also do you think could be posible to hide the part of the ParentTableID?

              • 4. Re: Validates values when entering records in a portal

                Sounds very possible to set up in a portal. The portal has a scroll bar and if you take a closer look as some of the selection portal options, they can also be used with this check box portal to pull up smaller sets or to search for a particular value.

                I believe that your new question has already been answered in another thread. You define a number field with the serial number auto-enter field option.

                • 5. Re: Validates values when entering records in a portal

                  Yes Phil, it is possible but the thing is that the weld numbers are not preset numbers, they must be created when needed (incrementally). So it won't be useful to use a portal to select a number that is already known.

                  I really like the idea you gave me (about the expression including the ParentID) the only thing is that I would like to have just simple numbers for the welds like 1,2,3,.., instead of 745|1, 745|2, 745|3. Looks like there is no other option than using the expression you told me or the script I have created. Btw, in your experience, does scripts like that one compromise the performance of the DB?

                  Thanks so much Phil!



                  • 6. Re: Validates values when entering records in a portal

                    You misunderstand. The weld numbers SHOULD be 1, 2, 3.

                    This added field is NOT the weld number. It's just a method used to make sure that you do not use the same weld number twice in the same portal. It's a different field.