11 Replies Latest reply on Aug 5, 2011 4:52 PM by sk

    Setting Field as selected in script?

    sk

      Title

      Setting Field as selected in script?

      Post

      Hi All,

      I'm trying to set a calculated field to "1" if it is the only related record.

      I have a raw materials table, suppliers table and a join table between them of rawmtrlsupplier.
      While in raw materials table, user can add one or more suppliers for a particular raw material, and see this in a related portal. In the portal of suppliers I have a field "rawmtrls_RAWMTRLSUPPLIER:: _MainSupplierID" with a checkbox set that has a value list of "1". It also has a button applied to it as shown below:

      Scripts: Set Default Supplier
      Set Error Capture [ On ]
      Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_rawmtrlsupplier_SUPPLIER::__kp_SupplierID ]
      Refresh Window

      It also has a calculation assigned to it which is:

      If (rawmtrls_rawmtrlsupplier_SUPPLIER::__kp_SupplierID = RawMtrls::_kf_DefaultSupplierID; 1; 0).
      This lets me set the RawMtrls _kf_DefaultSupplierID with the appropriate Supplier ID, so that I can use it for a script to find all of the raw materials related to supplier, so that a user can go to a pick window and add the items they want on a PO for that supplier. 

      How do I enable the checkbox being automatically selected when there is only one supplier in the portal?

      Right now the user can add a new supplier but if it is the only one in the portal then they also have to click on the field to select it as the main supplier. Seems counter intuitive to have to do this extra step if there is only one supplier in the portal.

      I have tried scripting in a find to the RAWMTRLSUPPLIER to see if the particular raw material only has one supplier and then set the rawmtrls_RAWMTRLSUPPLIER:: _MainSupplierID to have a value of 1, but because it is a calculation field, I can't modify it.

      What is a good way to approach this?

        • 1. Re: Setting Field as selected in script?
          philmodjunk

          How about a script like this?

          If [ Count ( rawmtrls_RAWMTRLSUPPLIER::_kf_SupplierID ) = 1 ]
             Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_rawmtrlsupplier_SUPPLIER::_kf_SupplierID ]
          End IF

          Use an onObjectExit trigger on the portal to this join table to perform the script automatically. Any action in the portal, including deleting a join record will perform this check to see if there is only one linked supplier and if so copy it to the default supplier ID in the raw materials table.

          Then you can use a calculation field much like you have already in the join table or layout text with a conditional format to identify the default supplier in the join table's portal.

          • 2. Re: Setting Field as selected in script?
            sk

            Thanks PhilModJunk,

             

            Could you elaborate further, or give an example, of what you mean in the last sentance "Then you can use a calculation field much like you have already in the join table or layout text with a conditional format to identify the default supplier in the join table's portal."

            I would like to stick with the checkbox interface.

            • 3. Re: Setting Field as selected in script?
              philmodjunk

              rawmtrls_rawmtrlsupplier_SUPPLIER::__kp_SupplierID = RawMtrls::_kf_DefaultSupplierID

              is all you need in the calculation field. You can format it with a single value check box group with 1 as the sole value.

              You can then set this check box field up as a button to perform this script:

              Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_rawmtrlsupplier_SUPPLIER::_kf_SupplierID ]

              Then, when you click this button, the supplier ID number from that portal row's record is copied into the default ID field in the raw material record, and this in turn causes the check box field to update and show that this supplier is the default supplier. The check box field for any previously selected default supplier will automatically update to 0 and this clears the check box on that portal row.

              • 4. Re: Setting Field as selected in script?
                sk

                Something is not working when the first initial supplier is made for a raw material. It does not set the field.

                I set an onObjectExit trigger directly to the supplier portal on the raw materials layout as suggested with this script:

                script: RawMtrlSupplier_maincheck

                If [ Count ( rawmtrls_RAWMTRLSUPPLIER::_kf_supplierID ) = 1 ]
                Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_rawmtrlsupplier_SUPPLIER::__kp_SupplierID ]

                End If 

                The only thing I changed from your suggested script was to change the name of the primary key field for supplier.

                Any ideas of what I maybe missing?

                • 5. Re: Setting Field as selected in script?
                  philmodjunk

                  It should update when you exit the portal row. Do you get different results if you make commit record the first script step?

                  BTW, the __kp_SupplierID would be the name of the primary key in the SUPPLIER table, not in the join table. Here, it's a foreign key and following your naming convention should be prefaced with _kf_ instead of __kp_.

                  • 6. Re: Setting Field as selected in script?
                    sk

                    I mean in this part I changed it (see Bolder text)

                    If [ Count ( rawmtrls_RAWMTRLSUPPLIER::_kf_SupplierID ) = 1 ]
                       Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_rawmtrlsupplier_SUPPLIER::__kp_SupplierID ]
                    End IF

                    As I figured we are in the SUPPLIER table at this point so it grabs the primary key. 

                    Unless, I'm suppose to make a foriegn key in the suppliers table that is "_kf_SupplierID"?

                    • 7. Re: Setting Field as selected in script?
                      philmodjunk

                      Yeah, that's the join table. not the supplier table right? At least that's what I thought that table was, but then maybe I've misinterpeted your naming conventions. If it is, I'd replace the reference to the supplier table with a reference to the foreign key to the supplier in the join table. There's no need to refer across the extra to to the supplier.

                      And did I assume correctly that the portal refers to the join table, not the supplier table?

                      • 8. Re: Setting Field as selected in script?
                        sk

                        Hi Phil,

                        Thanks for the help. I've tried various things and still no luck. The join table is "RAWMTRLSUPPLIER".

                        The onObjectExit script trigger is now set as:

                        If [ Count (rawmtrls_RAWMTRLSUPPLIER::_kf_supplierID) = 1 ]

                        Set Field [ RawMtrls::_kf_DefaultSupplierID; rawmtrls_RAWMTRLSUPPLIER::_kf_supplierID ]

                        End If

                        I tried creating a button on the raw material layout to just run the abooive script and it works that way. Not sure why the onObejctExit script trigger doesn't work. Any further ideas?

                        • 9. Re: Setting Field as selected in script?
                          philmodjunk

                          Then the If Step should be:

                          If [ Count ( rawmtrls_RAWMTRLSUPPLIER::_kf_supplierID ) = 1 ]

                          • 10. Re: Setting Field as selected in script?
                            sk

                            Is that not what I have already?

                            I've tried quitting and restarting Filemaker to see if that by some off chance made a difference, but still no luck.

                            • 11. Re: Setting Field as selected in script?
                              sk

                              I made a calculation in the raw material table to count the related rawmtrlsuppliers, and that shows the correct count, so at least I know that the count is correct. This one has me very puzzled. I updated my OS to lion and so I checked to see if this is a possible known issue, but it is not listed. Don't have access to another computer at the moment running some other OS to test, but I can on Monday. Maybe worth a shot.