13 Replies Latest reply on May 21, 2015 2:12 PM by humitec

    Get ITEM ID from related table -Inventory Starter Solution

    humitec

      I am trying to replicate the Inventory Starter Solution into my own solution.  I have recreated everything minus a few fields from the starter solution in my own and have it working 98%.  The problem I am having is the portal records are not showing, and I also cannot edit/add any records in the portal.  After many hours of checking and double checking I have figured out the problem which I'll try to explain below.

       

      My related table (Inventory Transactions::ITEM ID MATCH FIELD)  is supposed to automatically get the ITEM ID MATCH FIELD from the main Inventory table when a new record is created.  Currently nothing populates

       

      In the Starter Solution the ITEM ID MATCH FIELD for the Inventory Transactions uses a calculated value referencing $$CURRENT_ITEM_ID which I finally realized is a variable that is set in a script.  There are actually two scripts that sets this variable in the starter solution depending on the device used.

       

      Is there a easier way to get the automatically generated number from " Inventory::ITEM ID MATCH FIELD" to populate in the "Inventory Transactions::ITEM ID MATCH FIELD??

        • 1. Re: Get ITEM ID from related table -Inventory Starter Solution
          erolst

          humitec wrote:

          Is there a easier way to get the automatically generated number from " Inventory::ITEM ID MATCH FIELD" to populate in the "Inventory Transactions::ITEM ID MATCH FIELD??

          It depends on what you consider to be “easier” within your particular workflow; you could

           

          • capture this Item primary key in a $var while in the Item context, then set it explicitly on record creation in the Transaction table

          • use a portal based on a relationship between Item and ItemTransaction where “Allow creation … ” is checked; new portal rows (i.e. related records) will receive the parent key(s) automatically

          • adapt to the Starter Solution approach and make sure that the required $$var is set

           

          As an aside, be aware that there easier to read, yet more informative nomenclatures than than SOMETABLE ID MATCH FIELD:

          • id (primary key), id_parentTable (foreign key)

          currentTable_ID_PK, parentTable_ID_FK

          etc.

          • 2. Re: Get ITEM ID from related table -Inventory Starter Solution
            siplus

            the inventory starter solution uses the "Allow creation" thingie that erolst already mentioned.


            The portal will have an extra empty line, with a trash icon on its right, which kinda sucks (as the "do you want to permanently remove" with no additional info msg does, imho). It is totally not consistent btw, because clicking it on the last portal row accomplishes nothing.


            When you click on the add transaction button, you know the "Inventory::ITEM ID MATCH FIELD" so you can pass it as a parameter to your transaction-creating script and use it.


            And yes, I 110% agree with Erolst about field names with spaces in them: writing ExecuteSQL lines becomes an unneeded arthritis exercise as you wrap them around \" 's.

            • 3. Re: Get ITEM ID from related table -Inventory Starter Solution
              Stephen Huston

              Also check that your calculated field result is fully Stored if you are using it in a relationship join.

              • 4. Re: Get ITEM ID from related table -Inventory Starter Solution
                humitec

                Thanks everyone for your responses. I will go back again and try to figure it out.  With what knowledge I have I agree with @erolst and the field name concerns. Those are just the names that FM chose in their starter solution. I kept the same names (for the meantime) while trying to figure out their code.

                • 5. Re: Get ITEM ID from related table -Inventory Starter Solution
                  keywords

                  I don't know about the Inventory starter solution, but from my observation of at least one of the other starter solutions, there is a dependence on setting global variables via script in order for the solution to work, and it is easy to bypass creation of these simply by navigating to a layout by a method other than the button that sets the global variable. The starter solutions give a novice something they may be able to use, but they don't model ideal or recommended practice. If you want to turn this into a functioning DB I would recommend you find another way to run the relationship that does not depend on global variables.

                  • 6. Re: Get ITEM ID from related table -Inventory Starter Solution
                    humitec

                    Do you have any suggestions on another way to make the relationship work? I am looking for an easier solution.  It does use a global variable that is set in a script which is set to the key field of my Inventory table. BUT that isn't working...

                     

                    All I need is the key field in the Inventory Transactions table to automatically populate with whatever the key field is in the main Inventory table.

                     

                    I have tried many other options like the lookup function , but no luck there either.  This seems like a very issue simple fix, but I can't make it work.

                    • 7. Re: Get ITEM ID from related table -Inventory Starter Solution
                      keywords

                      Why not use a standard primaryKey = foreignKey relationship, with the Transaction records created directly in the portal, with this portal on an Inventory table layout?

                      • 8. Re: Get ITEM ID from related table -Inventory Starter Solution
                        erolst

                        Post #2 lists a number of options, where “automatically” = portal based on an “Allow creation” relationship.

                        humitec wrote:

                        many other options like the lookup function

                        For that to work you'd need the very relationship you want to create – Catch-22…

                        • 9. Re: Get ITEM ID from related table -Inventory Starter Solution
                          siplus

                          it does use a global variable that is set in a script which is set to the key field of my Inventory table. BUT that isn't working...

                           

                          does that global variable begin with $ or with $$ ?

                          • 10. Re: Get ITEM ID from related table -Inventory Starter Solution
                            humitec

                            siplus wrote:

                             

                            it does use a global variable that is set in a script which is set to the key field of my Inventory table. BUT that isn't working...

                             

                            does that global variable begin with $ or with $$ ?

                            yes it is $$CURRENT_ITEM_ID which is found in 2 different scripts depending on the layout style used.  Here is the script from the starter solution, which has many scripts based on the screen size/layout used.  I did make the change for the layout used.


                            Screen Shot 2015-05-21 at 6.43.11 AM.png

                             


                            • 11. Re: Get ITEM ID from related table -Inventory Starter Solution
                              humitec

                              I do not have the option for "allow creation" on my portal setup.  The portal will allow me to edit the row if I manually  key in the same  ID number generated from the Inventory table into the "key field"in Inventory transactions .  The problem is that number populating automatically when you add a new record in the Transaction layout 

                              • 12. Re: Get ITEM ID from related table -Inventory Starter Solution
                                keywords

                                You need to configure the relationship to allow creation of related records:

                                 

                                Screen Shot 2015-05-21 at 10.32.11 pm.png

                                 

                                This should be in a standard PK = FK relationship, not one driven by a global variable.

                                • 13. Re: Get ITEM ID from related table -Inventory Starter Solution
                                  humitec

                                  I never paid attention to the "=" in the relationship graph. I have spent DAYS trying to figure out this problem.  Thank you everyone for your help!