1 2 3 Previous Next 93 Replies Latest reply on Mar 14, 2013 8:54 AM by PeterMontague

    How to make a new sku if the product-id is new and go to related record if it product-id is already...

    PeterMontague

      Title

      How to make a new sku if the product-id is new and go to related record if it product-id is already present

      Post

           I am using a duplicate of my inventory table called Inventory 2 to check that a product-id exists already in my database. I want to add new skus if the product-id doesn't already exist. I have a script which checks the duplicate occurrence of the inventory, Inventory 2, and I want it to make a new sku if the product-id isn't already there. I thought my script, below would do that for me. If the product id is already there it adds the old sku. But I want it to make a new sku if the product-id is new.
           Also if the product-id is already in the inventory I'd like my database to take me to the related record rather than allowing me to make a new record with the same sku.
           Here is my script. 
                
                          Set Field [Inventory 2::product-id ; Inventory::product-id]
                               Set Field [Inventory::sku ; Inventory 2::Sku]
                               Commit Records/Requests [No dialog]
           

            

      relationships.JPG

        • 1. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
          philmodjunk

               Are inventory and inventory 2 separate tables or two occurrences of the SAME table?

               They look like two occurrences of the same table in your screen shot, but that makes no sense for the script that you have posted here.

               If I understand correctly what I am seeing here, you need a match to TableByISBN just by product-id not product-id and sku. I don't see why you match by both here, but if it's necessary, a new occurrence of TableByISBNcan be linked to inventory so that you can use this relationship to see if a sku has been generated for a given product id (ISBN isn't it?).

               IsEmpty ( TableByISBN::sku ) will be true if there is no sku for that product-id IF the relationship to TableByISBN matches soley by product-id.

          • 2. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
            PeterMontague

                 Inventory and inventory 2 are two occurrences of the same table.

                 TableByISBN is where I want to store an individual record for each copy of my books. If I've 10 copies of sku 1 there will be 10 records here. This is to help me with location. I can't remember why I called the table TableByISBN. But that is what it is called now. Anyway the table has no records initial at the moment. 

                 

                      IsEmpty ( TableByISBN::sku ) will be true if there is no sku for that product-id IF the relationship to TableByISBN matches soley by product-id.

                 Will this work if I apply it to the duplicate table inventory 2? Or what sort of table will I need to set up a relationship with to make an adaptation of your script work? I dont have any need to link my inventory table to its duplicate by sku as well as product-id. Product-id is just what i need.

            • 3. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
              philmodjunk

                   sku is an auto-entered serial or auto-entered Get ( UUID ) in InventoryTableByISBN?

                   Then this should be set up to work from a layout based on TableByISBN instead of inventory. And the expression becomes IsEmpty ( Inventory::SKU ), though you may want a different occurrence than inventory if there is truly a use for the current relationship that matches on both sku and product-id.

                    

              • 4. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                PeterMontague
                     

                sku is an auto-entered serial or auto-entered Get ( UUID ) in InventoryTableByISBN?

                I was trying to make sku an auto-entered serial in Inventory 2. But I can't seem to get access to any fields in this table. 

                I want to make Inventory the starting point for the script. I'd like Inventory to refer to another table to which it would be related by the product-id field. I had envisaged that this other table, preferably Inventory 2, would have an auto-entered serial to make the new (if needed) sku.

                I've tried out something like this: 

                If [ IsEmpty (Inventory2::sku)]

                Set Next Serial Value [Inventory::sku; 10000]

                End If.

                But that is not creating new serial numbers for me.

                I've made the Inventory to TableByISBN relationship have only one set of key fields: sku.

                • 5. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                  philmodjunk
                       

                  I was trying to make sku an auto-entered serial in Inventory 2.

                  Pardons on the last post. Re-reading things shows my confusion here. I keep mixing up SKU and product-id in the back of my head.

                  sku identifies a specific copy of a book. product-id identifies a specific book title (and I would assume for a particular format for that title such as paper back vs hard cover, vs audio-books...)

                       Correct?

                       If so...

                       The issue here does not appear to be a need to create a new sku, that should happen automatically each time you create a new record in inventory. It appears to me that the issue is to detect when there is no matching record in TablesByISBN and to create one with the corred Product-id when needed. From Inventory, you can use IsEmpty ( TablesByISBN::${product-id} ) to detect when a new related record is needed in that table.

                       I don't see any purpose for the inventory 2 table occurrence.

                       If I have all that correct, there should not be a sku field in TablesByISBN as it makes no sense for that table.

                  • 6. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                    PeterMontague
                         

                    sku identifies a specific copy of a book. product-id identifies a specific book title (and I would assume for a particular format for that title such as paper back vs hard cover, vs audio-books...)

                         

                              Correct?

                         Actually sku identifies a specific title in a specific item condition.

                         Product-id identifies a specific title in a specific format. There can be various skus for the product-id. E.g a product-id in new condition will have a different sku to one in very good condition.

                         Sku child is the one which identifies a specific copy of a book. There may be 1000 sku childs related to one sku in different locations.

                         I have changed the name of the table TableByISBN to sku child to make things more clear.

                    • 7. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                      philmodjunk

                           Let me see if, finally, I have this user scenario correct:

                           You go to a layout based on Inventory to add a new copy of a book to the inventory.

                           You create a new record and enter the ISBN as the product-id.

                           Then, if a sku that is correct for this copy is already in "sku child", you select that sku. If no such sku exists, one needs to be created.

                           -----------------------------

                           What method do you currently use to select a sku when one is available?

                           We'll need to modify this selection method to provide an option for adding new skus when needed. This might be as simple as including an " +add" value to a value list of skus or it might be provided as a button to click. A relationship to a new occurence of child sku, BTW, is likely to be helpful:

                           Inventory::product-id = skusSameISBN::product-id

                           this can produce a conditional value list of all skus that currently exist for a given ISBN value.

                           And I still don't see a use for using inventory 2 in this situation. It may be useful to see a list of all copies in inventory with the same ISBN number as the current record on your inventory layout, but it's not going to help us generate new skus when needed.

                      • 8. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                        PeterMontague

                             You are correct Phil. That is my scenario. 

                             

                        Then, if a sku that is correct for this copy is already in "sku child", you select that sku. If no such sku exists, one needs to be created.

                             This method could work for me. But at the moment my sku child table has no records in it. I'm also developing a way to populate this table. You advised me on writing a script to make make multiple records, depending on the quantity of an sku. I still have some questions about that which I will keep to that thread. I wanted to get the creation of new skus and GTRR up and running before I attempted to get the child sku table running.

                             

                        What method do you currently use to select a sku when one is available?

                        I was simply doing a find to see if the product-id was present. If it wasn't I created a new record which automatically made a new sku.

                        • 9. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                          philmodjunk

                               I'm going to repeat this question with more detail:

                               How do you SELECT that sku? I understand how you are determining if it exists, but not how you enter it into the sku field for the new record?

                               Where I am headed with this is that I think you can set up a conditional value list by using a relationship that matches to an occurrence of sku by product-id (ISBN). This way, you can get a drop down list of skus for just that ISBN number. If you see the sku in the drop down list, it exists and you can select it. If it does not exist, you can either click a "new sku" button to perform a script that does this, or you can add what I call a "control value" to your conditinal value list that you can select to add a new sku. This last bit exploits some lesser known aspects of table based value lists but is quite professional looking when fully set up. You select this "control value" and an OnObjectSave trigger controlled script then detects this value and does the needed steps to add a new sku in the skus table and then copy it to the sku field of the new inventory record.

                               Do you know how to set up conditional value lists?

                          • 10. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                            PeterMontague

                                  

                                 

                                      How do you SELECT that sku? I understand how you are determining if it exists, but not how you enter it into the sku field for the new record?

                                  

                                 Up till now I had been making sku an auto-entered serial number, which increased in increments of 1. It had a unique value and was always numeric. 

                                 

                            Where I am headed with this is that I think you can set up a conditional value list by using a relationship that matches to an occurrence of sku by product-id (ISBN).

                            I really like that idea.

                                 

                            Do you know how to set up conditional value lists?

                            Is that the same as setting up value lists based on fields?

                            My child sku table has no records in it yet. I presume I'll have to fill that with records to make it work as a table where I can check and see if the sku exists already.

                            • 11. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                              philmodjunk
                                   

                                        Up till now I had been making sku an auto-entered serial number, which increased in increments of 1. It had a unique value and was always numeric.

                                   But in which table? THis makes sense in sku child, but not if defined in Inventory--since you will have multiple records in inventory with the same sku. When you add a book to inventory, presumably, you then must either select an existing sku for it or create a new record--which generates that new sku in Sku child.

                                   

                              Is that the same as setting up value lists based on fields?

                              Not quite. A conditional value list is based on fields, but either the field it's based on is a calculation field or the value list uses the "include only related values" option to limit the listed values to only a specified subset of all the values in the table from which the values are drawn. (Select "vegetable" in a category field used in the relationship and the list shows: "Carrot, Beet, Radish". Select "fruit" and the list shows "Apricot", "Apple", "Kiwi".) In your case, a relationship between inventory and an occurrence of sku child matching only by ISBN can be used to produce a value list of just the skus that exist for that ISBN.

                              Check out the list of links on conditional value lists that I am going to add to the end of this post to learn more about this method. If you can set up a value list of just the existing skus for a given ISBN, I can show you how to add in a "control" value if you want to use that method for telling FileMaker to create and select a new sku.

                                   

                              My child sku table has no records in it yet

                              Populating that table is very much the next thing that you need to do here. Here's a way that you can do that, assuming that the skus in Inventory are the values you want to use as skus once you have made this change:

                                   In Child Skus, give Sku these validation field options: Unique Values; Validate always. Now use Import Records to import all your records from Inventory into Child Skus, mapping Sku fields and anyother fields in inventory such as ISBN and book title that need the same value in child skus. This validation rule will filter out any records with duplicate skus so that you get only one record for each sku. BTW, for any fields in child skus NOT used like sku in relationship matches that now hold the same data as a field in inventory, the inventory field is no longer needed and can be removed from the table. (But do this a bit at a time, updating layouts so that fields don't disappear off your layouts. You'll need to repoint those field objects to the corresponding fields in child skus.

                                   Ps. "child skus" seems to me to be misnamed as this is really the parent of your sku values.

                                   Links on conditional value lists: (the 1st and 2nd links have a lot of overlap.)

                                   Forum Tutorial: Custom Value List?

                                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                                   Hierarchical Conditional Value lists: Conditional Value List Question

                              • 12. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                                PeterMontague

                                      

                                     
                                          But in which table? THis makes sense in sku child, but not if defined in Inventory--since you will have multiple records in inventory with the same sku. When you add a book to inventory, presumably, you then must either select an existing sku for it or create a new record--which generates that new sku in Sku child.
                                     Inventory now has only unique skus in it. My plan was that these unique "parent" skus would have their multiple counterparts in the child sku table. If a new sku is needed for ten copies of a book. I planned on making one new record in Inventory with a unique sku and ten records in child sku, all with the same sku and ten unique "sku child"s. 
                                     Am I right that in thinking I should do this instead: populate the child sku table and rename it "parent inventory". I should make multiple copies of skus in Inventory as needed and have only the "parent" sku in "child sku". 
                                      
                                          To get back to your earlier question:
                                           
                                          
                                               How do you SELECT that sku?
                                     
                                           
                                          I'm away from the computer and improvising but in the above scenario I would use something like this: 
                                           
                                          If( Is Empty ( parent inventory::sku ; serial number// I'm not sure of the function for this yet// ; parent Inventory::sku))
                                      
                                     I'll read those links now. 
                                      
                                • 13. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                                  PeterMontague

                                       I've written a set field script step.

                                       The step is as follows and is working: If ( IsEmpty ( Parent Inventory::sku ) ; SerialIncrement ( Inventory::sku ; 1 ) ; Parent Inventory::sku )

                                       How do I choose the next serial number in this script step?

                                  • 14. Re: How to make a new sku if the product-id is new and go to related record if it product-id is already...
                                    philmodjunk

                                         Then I'm afraid that we've chased ourselves all the way around the bush to end up with what I originally assumed to be the case till a post form you indicated otherwise:

                                         I posted:

                                         

                                              Let me see if, finally, I have this user scenario correct:

                                         

                                              You go to a layout based on Inventory to add a new copy of a book to the inventory.

                                         

                                              You create a new record and enter the ISBN as the product-id.

                                         

                                              Then, if a sku that is correct for this copy is already in "sku child", you select that sku. If no such sku exists, one needs to be created.

                                         You responded:

                                         

                                              You are correct Phil. That is my scenario.

                                         Apologies, but what you have just posted reverses that. If you receive a shipment of 5 copies of a book, you are now indicating that you will need to add 5 new records to Child Sku, not Inventory.

                                         Before I go back and try again from this new understanding, Are you sure that this is correct?

                                         If so, I have to take several of the last posts and swap child sku for inventory.

                                          

                                    1 2 3 Previous Next