13 Replies Latest reply on Jun 25, 2016 6:03 AM by Wicktor

    a specific case for dwindling list ?

    Wicktor

      Hello everyone,

      my friend not speaking fluent english asked me this question.

       

      His solution starts with a layout of a Table with 300 text fields which should have only one record.

      In each field the user should enter specific numbers or letters (generated in a related Table).

      His need is to avoid the user to enter the same digit in different fields (accomplished with or without a value list).

      Any suggestion ?

      Thank You,

      Victor

        • 1. Re: a specific case for dwindling list ?
          mikebeargie

          First off, a table with 300 fields seems oddly non-normalized. If you can post a sample of the table, it may make more sense to structure the table differently.

           

          Secondly, there are two types of validation you can do in FileMaker, field-level validation and script-triggered validation. I would actually recommend the second one to you, as field-level will only take place when saving the record, which may not happen when you move from field to field.

           

          So you would be looking at the onObjectExit script trigger, and a script that evaluates the current field against the previous fields.

           

          A script could look something like:

          If [ Get(ActiveLayoutObjectName) = "field1" ]

             If [ //field1 validation condition <> 1 ]

                Show Custom Dialog [ "ERROR" ; "Field 1 is not valid" ]

                Exit Script [ False ]

             End If

          Else If [ Get(ActiveLayoutObjectName) = "field2" ]

            If [ //field2 validation condition <> 1 ]

               Show Custom Dialog [ "ERROR" ; "Field 2 is not valid" ]

               Exit Script [ False ]

            End If

          Else If [ etc... ]

             etc...

          End If

           

          This would be lengthy for 300 fields, but would allow you to customize the validation condition of each of the 300 fields, and exit without allowing proceeding through the form.

           

          You could also do pre-validation with an onObjectEnter script step to make sure that previous fields have been completed to specification before allowing a user to activate a field on the form.

           

          Lastly, I would look into virtual value lists set up with the onObjectEnter script step, otherwise your relationship graph will become unusable trying to make so many related/filtered value lists.

          • 2. Re: a specific case for dwindling list ?
            Wicktor

            Hi Mike

            thank you for your exhaustive explanation.

            I have not the permission of sharing a copy of the two Tables but they look like exactly as follows:

             

            TableA (the primary Table)

            Link = 1 (calculated)

            Field1 (text)

            Field2 (text)

            Field3 (text)

            Field4 (text)

            ...etc...

            Field 300 (text).

             

            TableB (the values Table)

            Link = 1 (calculated)

            Values (text)

             

            I did think about a trigger as your suggest onObjectExit, but for 300 fields....it is a nightmare.

            Do you think it is possible to have the value list (which is the same for all the 300 fields) automatically hide those values already entered ? I was thinking about a dwindling technique (which I have seen at work but never used)

            • 3. Re: a specific case for dwindling list ?
              mikebeargie

              Yes, it's possible, however you still would have to trigger the update of the value list somehow after each field is entered, so your nightmare would continue.

               

              If you could pivot the table so you have 300 records instead of 300 fields, then possibly you could do smarter logic with less work.

              • 4. Re: a specific case for dwindling list ?
                Wicktor

                Hi Mike,

                my friend explained me some critical details about his solution...at least I can understand why he wants 300 fields.

                His solution is about to manage his restaurant reservations and the 300 fields represents the physical disposition of the restaurant tables.

                So in his view,

                TableA is a unique record showing all tables,

                TableB is the value list with numbers of tables (1A, 1B, etc)

                and he has a TableC (related to TableA) which is the reservations for each table (name, date, time).

                He wants to represents restaurant tables using button bars with icons (conditionally calculated so grey are empty tables and red are busy tables).

                 

                Now he just gave me a copy of his file.

                I will try to change his solution having in TableA 300 records instead of 300 fields.

                 

                I will need, if you have time, suggestions how to design the dwindling list since I never did one before.

                Thank you for your attention !

                • 5. Re: a specific case for dwindling list ?
                  mikebeargie

                  That's not a great way to do things. Essentially instead of having a record for each "table" (seating area) with fields for location, number of seats, etc.. He has created a field for each of them.

                   

                  This is de-normalized data that is just difficult to work with.

                   

                  If he changes TableA to just have a record for each table, instead of a field of each table, then you should be able to build a system easier from that. You would be able to get rid of TableB completely.

                   

                  Essentially, you have "Tables" and "Reservations", that's it. From there you should be able to build a calendar showing when each Table record is reserved based on a relationship from the Reservations table. And you should be able to build a system that only shows related "open" tables when creating a new reservation.

                  • 6. Re: a specific case for dwindling list ?
                    Wicktor

                    I did it.

                    only two Tables: "Tables" "Reservations", linked with "table" field (allowing creation of records on "Reservations")

                    On first run itself generates 300 records on "Tables".

                     

                    He likes to have a layout where he can visualise all 300 "Tables" together using Buttons.

                    I guess I will do that on "Tables" using for each records the GetNthRecord function ?

                    • 7. Re: a specific case for dwindling list ?
                      siplus

                      An answer to your original question, n fields that must have different values. (For the rest, Mike is ofc right and you should follow the path he indicated).

                       

                       

                      1) Implement this custom function:

                       

                      FileMaker Custom Function:UniqueValues( values )

                       

                      2) create a calc field myFieldList defined as List(yourfield_1; yourfield_2; .... yourField_n)

                       

                      3) if valueCount(myFieldList) = valueCount(UniqueValues(myFieldList)), there are no fields that contain the same value as other fields, otherwise yes.

                      • 8. Re: a specific case for dwindling list ?
                        Wicktor

                        hi siplus

                        you mean step 3 as a trigger for each field right ?

                        • 9. Re: a specific case for dwindling list ?
                          siplus

                          no, I mean it as a trigger on the record commit. Or as a check to be done when you click submit.

                          • 10. Re: a specific case for dwindling list ?
                            Wicktor

                            Ok Thank You !

                            at least helping this friends I learn more

                             

                            I need now to solve his problem of having all the 300 records on same layout each with icons (no portal).

                             

                            About this thread who should get "Correct Answer" or "Helpful", you or Mike ?

                            • 11. Re: a specific case for dwindling list ?
                              Wicktor

                              For having all the 300 records of the same filed showing in the same layout (without using a portal) do I necessarily need to use the GetNthRecord function ?

                              • 12. Re: a specific case for dwindling list ?
                                siplus

                                I guess you mean 300 fields, not records. At least, standing to the original post.

                                • 13. Re: a specific case for dwindling list ?
                                  Wicktor

                                  No, records.

                                  I did change my friend's solution accordingly with Mike suggestion: only two Tables: "tables" and "reservations", linked with "table" field (allowing creation of records on "Reservations")