7 Replies Latest reply on Nov 7, 2014 12:39 PM by philmodjunk

    Concatenate Record IDs into Global for Filtering Inventory



      Concatenate Record IDs into Global for Filtering Inventory



      I've got a simple inventory system to track individual, serialized products (actually, barrels). Each barrel is unique and must be tracked individually, but can be assigned to a Customer. Relevant tables include:

      • Inventory (for each barrel)
      • Companies (for customers and vendors)
      • CustomerPOs
      • BIN Company Info (holds Globals for filtering)

      Unique record IDs are present for Inventory, Companies, and Customer POs. Foreign keys are also present for relating them all together and it seems to work fine. 

      I want to easily sort through what inventory is available and what is already assigned to certain customers. So, I've got a layout based on "BIN Company Info" table that uses global fields to filter inventory based on things such as location, status, minimum age, and customer. The "Bin Company Info" table is related to an Inventory table occurrence via a bunch of AND statements between the Globals and their relevant counterparts. For the barrel owner's (i.e. the customer), this is FKCustomerID in the Inventory table = FilterCompanyID in Bin Company Info table. But filtering based on customer doesn't quite work yet.

      I'd like to be able to use a popover menu, drop down, or similarly easy method to select one, all, or none of the companies and thus view who owns which barrels and which barrels are "available" for assignment. I can do this successfully and select a single company at a time to filter by, but I can't figure out how to do this for all (or no) companies with the same filter field--how do I add all of the FKCustomerIDs in the Inventory table to the FilterCompanyID Global field, which is what controls the results?

      I thought a button next to the popover that ran a script to add all FKCustomerIDs to the Global filter variable would work, but I couldn't figure out how to loop through the Inventory table, collect each unique FKCustomerID, then append each of those IDs to the Global filter.

      Probably a pretty simple script technique (if a script is the best way to go here), I just can't seem to find it. Any help is appreciated!


        • 1. Re: Concatenate Record IDs into Global for Filtering Inventory

          I'd use an ExecuteSQL calculation to produce the needed return separated list of values:

          ExecuteSQL ( "Select ( \"FKCustomerID\" FROM \"Inventory Table\" " ; "" ; ¶ )

          will produce a return separated list of all customer IDs from your inventory table.

          • 2. Re: Concatenate Record IDs into Global for Filtering Inventory

            PhilModJunk, thanks for the quick answer.

            Would you add this as the button's action (Execute SQL, then add "ExecuteSQL ( "Select ( \"FKCompanyID\" FROM \"Inventory\" " ; "" ; ¶ )" as the SQL Text)?

            I just tried that and it didn't seem to work. On the layout I have a popup for selecting an individual company (from a dynamic value list based on CompanyIDs). Next to that I have the button that I added your command to, hopefully to replace the Global with all FKCompanyIDs in the inventory table. Choosing a single company in the popup menu has always worked, but adding that command to the button did not replace the FilterCompanyID Global with all of the FKCompanyIDs. 

            I tried adding "BIN for Company Info::FilterCompanyID  = " to the beginning of the SQL statement to see if that helped, but it did not. Not too familiar with SQL commands, so not sure what to tweak going this route.

            • 3. Re: Concatenate Record IDs into Global for Filtering Inventory

              This can be defined as an unstored calculation field used as the match field in your relationship.

              No script needed.

              If you used a script, you would use set field, not execute SQL as your button option. The "calculated result" part of set field would then be this executeSQL function call.

              Note: there is a script step (buton option) named Execute SQL and there is a function named ExecuteSQL(). They are not the same thing and I am describing use of the function call, not the script step.

              • 4. Re: Concatenate Record IDs into Global for Filtering Inventory


                That seemed to do it. Although, it didn't work exactly as you posted with the quotes and escape characters. So I tried it without and it worked, as follows:

                ExecuteSQL ( "Select FKCompanyID FROM Inventory " ; "" ; ¶ )

                I was also able to reuse this handy bit of code to "mass fill" other Globals to filter by as well, so thanks!

                The only minor issue now is that after the button is pressed and field filled with all IDs, two sum fields from the related Inventory table, but displayed on this layout, do not update instantly. You have to navigate out of the current field (i.e. click anywhere else on the layout) to get them to update.

                Is there a way to "force" them to update at the same time the SQL code is run?

                • 5. Re: Concatenate Record IDs into Global for Filtering Inventory

                  Your mouse click is performing a commit records action. This can be scripted and possibly performed via a script trigger.

                  I would suggest that removing the escaped quotes did not fix the issue but that removing the word "table" did.

                  Using the escape, quote pair around each identifier allows the SQL to execute correctly even if you have a two word identifier, a SQL reserved word such as "Date" or characters such as a leading underscore in your table or field name. I included them as insurance in case what you posted as your names wasn't exactly those you are really using. Quoting names that don't need to be quoted should not have created any syntax errors.


                  • 6. Re: Concatenate Record IDs into Global for Filtering Inventory

                    If I'm using a button to preform the set field action directly, without a script, and specifying the eSQL you suggested as the calculation for that field, can I tack on a "commit" action somehow...or do I need to create a simple script, transfer the SQL into that, then end with a commit action?

                    • 7. Re: Concatenate Record IDs into Global for Filtering Inventory

                      Create the script and use the perform script action.

                      It is generally a better design approach to use perform script for all buttons even when the script is only a single step. It's very common to use multiple buttons that all perform the same action and if you later need to update what that button does, editing a single script updates the action for all the buttons that perform that script. If you used button options, you have to go to each button and update it individually.