6 Replies Latest reply on Sep 23, 2016 1:22 AM by gantoja

    Simple create related record using checkboxes

    gantoja

      Hi, this may be very simple but can't find yet a solution.

       

      I would like to use checkboxes to create and update records in a related table, instead of in a text field in the same table.


      I would like to put the possible values (value list of a checkbox list) as columns, so as not to repeat each possible value in each box.

       

      This would be to maintain a many-to-many relationships (where one is an inventory and the second is a limited set, but that can change over time, and I don't want to use tags because the list may change).

       

      What would be the best approach for using a portal and checkboxes and some script to show/populate/create record/update records?

       

      If you need more information please tell so.

       

      Thanks a lot! 

        • 1. Re: Simple create related record using checkboxes
          David Moyer

          Hi,

          I imagine using a global text field defined as a checkbox field assigned to your value list.  As you select the checkboxes, the underlying global text field is populated with a ¶-separated list of the selected values.  Then you would write a script that processes the list in this global field.  Clicking a "Submit" button would fire the script.

          1 of 1 people found this helpful
          • 2. Re: Simple create related record using checkboxes
            gantoja

            Thanks David, there are two things though that I am trying to do differently:

             

            1) As the text (values) of the value list are long, and I would like to edit many records in a "list layout", I would prefer to put the text of value list only in the "header" of the layout, and not to have them repeated in each record.

             

            2) how would be the script that processes the list in this global field and updates the related table?

             

            Thanks!

            • 3. Re: Simple create related record using checkboxes
              David Moyer

              regarding #2 ...

              would you be creating one new record per value selected?  Or what?

              1 of 1 people found this helpful
              • 4. Re: Simple create related record using checkboxes
                philmodjunk

                Say you have three tables:Invoices, LineItems, Products. This may not be exactly what you have, but should be close enough to figure out how to implement. A typical relationship for this is:

                 

                Invoices-----<LineItems>-----Products

                 

                For a check box style interface--usually only practical when the total number of products is limited, you can set up the following relationships:

                 

                Invoices-----x----Products---------LineItems

                Inventory::anyField X Products::anyField

                 

                Products::__pkProductID = LineItems::_fkProductID AND
                Products::gInvoiceID = LineItems::_fkInvoiceID

                ("creation" is enabled for this relationship)

                 

                gInvoiceID is a global field. The onRecordLoad trigger on your Invoices based layout updates this field with the current record's Invoice ID:

                Set Field [Products::gInvoiceID ; Invoice::__pkInvoiceID ]

                 

                Then you place a portal to Products on the Invoices layout and put a button in the portal row that runs a script. The script either creates a record in LineItems, or if such a related record already exists, it deletes it. This then mimics the behavior of a check box group and you can even place a tickmark image into your portal row, use Hide Object When to hide it if there is no related record in LineItems and then make it part of your button that adds/deletes a line item.

                 

                The Script works something like this:

                 

                If [ IsEmpty ( LineItems::_fkProductID ) //product is not selected on this invoice ]

                   Set Field [ LineItems::_fkProductID ; Products::__pkProducID ] --> the relationship will copy over the Invoice ID

                Else

                   #Lineitem exists, delete it

                   Go To Related Records [Show Only related records ; from table: lineItems ; using layout : "LineItems" (LineItems) ]
                    Delete Record [no dialog]

                   Go to Layout [original layout]

                End If

                 

                IsEmpty ( LineItems::_fkProductID ) , by the way, is the expression that you can use with Hide Object When to keep your tick mark image from showing when no item is selected. You may have to give the tickmark an object name and include a refresh object step in the above script to get a reliable screen update with each mouse click.

                2 of 2 people found this helpful
                • 5. Re: Simple create related record using checkboxes
                  gantoja

                  When the checkbox is selected then the record in the related table would be created (if it does not exist), and a simple textbox with YES/NO or some boolean value updated to TRUE (YES).

                   

                  When the checkbox is unselected then the record in the related table should be updated to update the value to FALSE.

                  • 6. Re: Simple create related record using checkboxes
                    gantoja

                    Thank you @philmodjunk,

                     

                    The invoices - lineitems - products is a very good analogy to the problem I am posting.

                     

                    Your solution is almost exactly what I was looking for, only that I would prefer to UPDATE THE EXISTING RECORD WITH A FALSE VALUE. 

                     

                    I will try and adapt the script you provided and send you all my feedback today.