5 Replies Latest reply on Jun 4, 2017 7:57 AM by petar_j@me.com

    create related records using a checkbox list as the input method

    petar_j@me.com

      Hi All

       

      I have searched everywhere for a solution to this problem with no suitable result. If it something that is not do-able then I am going to move on and stop wasting anymore time. I am between a beginner and intermediate skill level so I need to follow concepts rather literally!

       

      For my solution I am wanting an easy and quicker way for a users to be able to choose their options on a dynamic checkbox list and once the selection(s) are made, a calculation is done, and the related record is added to the related table.

       

      My problem is the following:

       

      I have Table A, Table B and Table C. I would like to click on a pop-over button in Table A (Layout based on Table A), and it display the 'Value List' generated by the values in Table C as a checkbox list. Once a selection is selected, a 'calculation Field' in Table B is activated and the related value is calculated using values from the related Table A and Table C. This process happens for each selection on the checklist. My calculation field is also set-up to display a "0" result is the checkbox is unchecked. Currently when I 'check' a selection in the checkbox list, the calculation ONLY works on the first item on the checkbox list and does not cycle through the other options on the checkbox list and does not update.

       

      The relationship I have set up is a follows:

      TableA-----<Table B>-----TableC

       

      TableA::_kpn_id = TableB::_kfn_id_TableA

      TableC::_kfn_id = TableB::_kfn_id_TableC

       

      Value List = TableC::_kpn and TableC::ItemName

      Calculation field = TableB::TotalValue= TableA::Qty*TableB::X*TableC::Value

       

      Any help with this would be greatly appreciated?

       

      Many thanks !

        • 1. Re: create related records using a checkbox list as the input method
          erolst

          A script to create a number of related records based on a selection can come in one of several variants (switch context, use a portal, use a magic key ...)

           

          Let's choose the one that requires the least amount of preparation. Let your checkbox field be A::checkbox

           

          Set Variable [ $selectionList ; A::checkbox ]

          If [ IsEmpty ( $selectionList ) ]

            Exit Script

          End if

          Set Variable [ $selectionCount ; ValueCount ( $selectionList ) ]

          Set Variable [ $idA ; TableA::_kpn_id ]

          Set Bariable [ $qty ; TableA::quantity ]

          Go to Layout [ B ( TableB ) ]

          Loop

            Exit Loop if [ Let ( $i = $i + 1 ; $i > selectionCount ) ]

            New Record/Request

            Set Field [ TableB::_kfn_id_TableA ; $idA ]

            Set Field [ TableB::_kfn_id_TableC ; GetValue ( $selectionList ; $i ) ]

            Set Field [ TableB::quantity ; $qty ]

          End Loop

           

          Note that the script copies the qty into the join table, to capture that value at the time of creation.

           

          Another consideration is to avoid duplicates, i.e. add the same C record twice to an A record.

           

          In the future, try to not use abstract names like A, B and C. Describing your real-world scenario may give valuable clues that aid in finding the best (or most appropriate) solution.

          • 2. Re: create related records using a checkbox list as the input method
            philmodjunk

            No need to loop through the selected values to create related records if you use the "Check boxes with scroll bars II" example in this teaching file:

             

            Adventures in FileMaking #2-enhanced value selection

             

            It may not look like it, but this example does support the many to many relationship that you describe, but it rearranges it in Manage | Database in order to make the act of creating the Join table (Table B in your example) record very simple.

             

            Note that this isn't a real check box group formatted with a value list, but rather a portal set up to look and function as though it were such a field. The portal makes the scroll bar option possible should you need such an option.

            • 3. Re: create related records using a checkbox list as the input method
              petar_j@me.com

              Hi

               

              Thank you for your prompt response

               

              Bottom line then, it is possible to do then!

               

              I see you your point about using abstract names it is a bit confusing so if it is not an imposition herewith the real life scenario:

               

              I have Table's named 'LineItems', 'FlatRateFringes' and 'ProjectFringes'. I would like to click on a pop-over button in the 'LineItems' row, and have it display a 'value list' of options which is derived from the values prepopulated in 'ProjectFringes'. The way I have this currently set-up to be displayed on the popover is by using the FlatRateFringes::_kfn_id_ProjectFringes field, formatted as a 'checkbox' list.

               

              Once the selection(s) are made, I would then like to run a calculation using the values from (LineItems::QTY * LineItems::X * ProjectFringes::Value) and then have these result(s) stored as related record(s) to the original LineItem record. I would also like it if it were possible that if the value in the checkbox is unselected, then I need that related record to be deleted.

               

              LineItems-----<FlatRateFringes>-----ProjectFringes

               

              'LineItems::_kpn_id' = 'FlatRateFringes::_kfn_id_LineItems'

              'ProjectFringes::_kfn_id' = 'FlatRateFringes::_kfn_id_ProjectFringes'

               

              Value List = 'ProjectFringes::_kpn_id and ProjectFringes::FringeName'

               

              I hope that it is a bit clearer and thanks again.

              • 4. Re: create related records using a checkbox list as the input method
                erolst

                petar_j@me.com wrote:

                 

                Once the selection(s) are made, I would then like to run a calculation using the values from (LineItems::QTY * LineItems::X * ProjectFringes::Value) and then have these result(s) stored as related record(s) to the original LineItem record.

                In my example, you select the entries to be turned into related records, and have the calculation take place by way of an auto-enter option – or a calculation field; it depends on whether the pertinent ProjectFringes value can change over time, and if you want/must capture the value at this point in time.

                 

                (Think Invoicing and product prices; if within a line item you'd reference the product price (by using a calculation field), any subsequent price change would falsify old invoices. But if you copy the price, such a changes has no impact.)

                 

                petar_j@me.com wrote:

                I would also like it if it were possible that if the value in the checkbox is unselected, then I need that related record to be deleted.

                Almost anything is possible; the constraints you may find yourself under usually stem from the requirement to build a sensible, intuitive and smart UI, rather than from deficiencies in the programming tools.

                 

                I recommend you also look at the file that philmodjunk posted.