9 Replies Latest reply on Apr 29, 2013 11:52 AM by CStovin

    Filtering Value Lists / Conditional Value Lists - based on available stock

    CStovin

      Title

      Filtering Value Lists / Conditional Value Lists - based on available stock

      Post

           As a newbie, My  Asset Management / BOM ?  solution I think is further along than my level of understanding thanks to the help I've been given from the forums here.  I feel pretty grateful that a community as such exists.  I still have not attempt to add on the BOM ( assembly / group KITS ) part to my Inventory as I feel I still need to work out some under the hood neccesities in the other tables.

           CONTACTS ----> ORDER -----> LINES <-------- INVENTORY

           LINES::items_out,  is an amount field in my LINES::Portal, which shows how many items of an INVENTORY item is being loaned  "out"  ( this is not a representation of the actual amount in stock )

           LINES::items_out, I also have displayed  on my INVENTORY item history layout in a portal....

           INVENTORY::amount_in_stock,  Is a value entered

           INVENTORY::subtotal_portal = SUM(LINES::items_out)

           INVENTORY::available = INVENTORY::subtotal_portal - INVENTORY::amount_in_stock

            

           When I return items in ORDER, the LINES::items_out  field clears,   which also updates the same field displayed in the INVENTORY layouts.  Although sometimes I am experiencing window flashing and not immediate updating in different layouts until a number of "clicks" happens in other fields.... Sorry I'm not sure what causes the slight lag... Doesn't always seem to happen that I'm aware of....

           ANyhoo... what I am after ::

           I have tried to create a Filtered field which would than be used in a value list - but did not work, got errors saying could not use this field as could not be indexed because was either related , or a calculation etc....

           In INVENTORY::available_choice  (calculation field)  =

           IF (

           available > "0" ; manufacturer_name & " " & product_name & " " & model_name )

      I only want the items in Inventory that are available " have a greater value than 0 " to appear as a selection in my drop down   _kf_products_id  which is in my LINES:: portal.    THe above calculation did not work... said can not be indexed

           the graph is LINES::_kf_products_id   ------>>  INVENTORY::_kp_products_id   ( with allow creation of records checked in LINES, no check on the deletion )

           _kf_products_id  -->   value list:: products = (from field) INVENTORY::_kp_products_id  ( first field)    ,  INVENTORY::available_choice   (second  field)          -----> only show values from second field.

           soooooooooooooo.... this did not work for me, but when I got rid of the conditional calculation and just made INVENTORY::available_choice  =  manufacturer_name & " " & product_name & " " & model_name

           it worked..... but shows everything in INVENTORY.

            

            

           Can anyone see what I might have missed ?

        • 1. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
          philmodjunk
               

                    sometimes I am experiencing window flashing and not immediate updating

               I would guess that you have a script executing at the time this happens. I suggest putting a freeze window at the start of the script to eliminate the "flashing". And you may need to end your script with some strategically placed Commit Recorsd and/or Refresh WIndow steps if you have fields that do not smoothly update to show the correct results.

               have tried to create a Filtered field...

               It's not that you are using a calculation but it's due to the fact that your calculation cannot be a store calculation as it references a field in a related table and calculations that do so cannot be stored. Unstored calculations cannot be indexed and thus cannot be used as the match field in the relationship you are trying to set up for your conditional value list, nor can it serve as the "sorted" field in a table based value list. (At least one of the two possbile fields you can specify for a table based value list must be sorted.)

               You'll need to find a way to pull the entire calculation into a single record in a single table. One way to have that happen is to add a number field to Products and have your script update it by setting it to the value of SUM(LINES::items_out) each time you create/delete/modify a Lines record. You can then set up a stored calculation in products that subtracts this number field from Amount_in_Stock to compute the current amount still in inventory.

                

                

          • 2. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
            CStovin

                 ok great thanks Phil.   Isn't this what I did here ?

            "One way to have that happen is to add a number field to Products and have your script update it by setting it to the value of SUM(LINES::items_out)"

                 INVENTORY::subtotal_portal = SUM(LINES::items_out)

                 INVENTORY::available = INVENTORY::subtotal_portal - INVENTORY::amount_in_stock

                 INVENTORY::available = INVENTORY::subtotal_portal - INVENTORY::amount_in_stock

                 There is no script here, is that why the problem occurs ?   IT is just straight Field defining in Manage Databe->fields->options

            • 3. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
              philmodjunk

                   If you use a script to set the value:

                   Set Field [INVENTORY::subtotal_portal ; SUM(LINES::items_out) ]

                   yes.

                   If you use a calcualtion field, no.

              • 4. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                CStovin

                     Hi Phil,

                     I was about to delve into fixing my conditional value list when I found out from another tutorial how one should use scripts to add/subtract  product quantities from inventory when an order is created.

                     The reasoning was that using calculations is fine for a bit, but will eventually slow down your database when the records start increasing.

                     In my work order I can change my  Lines::items_out   to include a script trigger which will subtract the amount from inventory,  but since my database is a " asset /  rental " type of system  how can I add back the amount to inventory when it is returned.

                     Currently in my Lines portal I have a date returned field for each line item.  When the date returned field has a value entered it "0"'s the Lines::items_out field.  When my inventory was set up to calculate the Sum of the Lines::items_out  and subtract that amount from the Inventory Product total everything worked simply.  

                     I thought perhaps I could create a script that would run after the date_returned field becomes populated that adds back the value in Lines::items_out to inventory,   but I thought what if by accident that date field gets changed numerous times,  than it would still continue to add back the amount.

                • 5. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                  CStovin

                       Not sure which way I should go ?

                        

                  • 6. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                    philmodjunk

                         My preference is to use both methods in support of each other. Both options have their drawbacks and advantages so you really need both to compensate for different potential weaknesses.

                         Scripts are vulnerable to producing erroneous totals. If you run the script in the wrong context, run it more than once for the same transaction or your script does not have sufficient flexibilty to handle all possible changes to the inventory, you may get incorrect inventory counts. The Inventory Starter Solution released with FileMaker 11 is one such case as I found that I could run the update script twice for the same transaction or not at all under other circumstances.

                         Calculation based totals tend to always produce the correct totals as they will pull all the data present in the transaction table--no matter what method was used to log that change, to compute an inventory total. So they avoid a lot of potential pitfalls that can occur with script based inventory updates, but you are correct that as your table of transactions to be summarized grows, a bigger and bigger slice of time is required to compute an inventory total. And this will eventually produce unacceptable delays in computing these totals.

                         What I would do is take a look at using a script to keep the transactions table "lean". The exact business volume you have will determine how often you need to do this, but you can periodically "condense" your transactions table by doing the following:

                         1) Export data from a selected date range of transaction records into an archive file so that you can check the "history" if such is ever needed.

                         2) Replace multiple transaction records for each inventory item with a single record recording the "current on hand" for that item computed from the records just exported to the archive table.

                         3) I'd definitely use a script to update a number field in the inventory table to show the current amount on hand, but I'd copy the calculated total of all related transaction records from this now "lean" transactions table to do that update. The purpose of this number field is two fold: a) It's a stored, indexed total and thus can be used in relationships for conditional value lists or to efficiently filter found sets/portals to list items that currently have specified inventory totals. and b) In a list or table view of multiple inventory items, you'll get a much faster screen update if you use this plain number field instead of a summary field or calculation field that has to update item by item as the screen refreshes.

                    • 7. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                      CStovin

                           Since  Inventory::available    =    Inventory::stock   -   Inventory::sum_product_portal      (  and sum_product_portal = Sum(Lines::items_out) )

                            I tried to create a script trigger that when   Inventory::available was modified that it would run a script to update a new indexed field ::

                           Set field (Inventory::available_choice ; Inventory::available )

                           but no value was ever put into available_choice because it needs to be active ( ie entered in browse mode my guess )  Is there another way to do this ?

                            

                           available_choice will be my indexed field which will than be used to check  later if it is  > 0 ,   so that I can define a conditional value list to only dislply inventory products whose   "available_choice is  > 0 "

                      • 8. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                        philmodjunk

                             This is not the way I would structure this process. See this thread for a transaction table based method for managing inventory: Managing Inventory using a Transactions Ledger

                             With this approach, you don't use a triggers set on the inventory fields, you use scripts performed from the context of the related transactions table where any event that changes inventory is logged by creating a new record.

                        • 9. Re: Filtering Value Lists / Conditional Value Lists - based on available stock
                          CStovin

                               thanks Phil I will study this, and work to implement.