14 Replies Latest reply on Jun 11, 2010 2:53 AM by Frinholp

    Showing only records that meet a condition in value lists

    Frinholp

      Title

      Showing only records that meet a condition in value lists

      Post

      Is there a way to only show a field from a record in a value list if a condition is met?

       

       I have a stock table where each item has a status field. I would like to base a value list only on records where the status is set to available and omit those whose status is set to unavailable.

       

      Thanks in advance

       

      Lee

        • 1. Re: Showing only records that meet a condition in value lists
          philmodjunk

          Look at option one in this thread:

           

          Here's a thread on setting up conditional value lists:

          Custom Value List?

          • 2. Re: Showing only records that meet a condition in value lists
            ninja

            Howdy Frinholp,

             

            In your value list setup, there is an option to "include only related values...starting from"

             

            Form a relationship with another table occurence that filters out the "unavailable", and use only values "starting from" that table occurence.

            • 3. Re: Showing only records that meet a condition in value lists
              Frinholp

              Thankyou both for your response.

               

              Sorry for the noob question here.

               

              I am aware on how to create a new table occurence. What I am unsure of is how to filter the table so only certain records from the original table populate this TO. I know this is probably very basic but I haven't a clue on how to do it. It would be fantastic if I could, as I can think of many more uses for this in my solution.

               

              Any help greatly appreciated

               

               

              • 4. Re: Showing only records that meet a condition in value lists
                philmodjunk

                Using a calculation field (option 1 in the thread I suggested) avoids this issue. The calculation field is empty unless another field (status) in the record has the desired value ("available"). Thus, a value list based on this calculation field only shows a sub set of all the values in the table. (All those with status = "Available").

                 

                Using the "Include Only Related Values..." option Ninja recommended (and spelled out as option 2 in the other thread), requires a relationship that filters out the undesired values.

                 

                In your case, you'd need a calculation field, cAvailable, set to return "Available" as it's value in your main table. Then your relationship would be defined as:

                 

                MainTable::cAvailable = Stocks::Status

                • 5. Re: Showing only records that meet a condition in value lists
                  Frinholp

                  PhilModJunk wrote:

                   

                  In your case, you'd need a calculation field, cAvailable, set to return "Available" as it's value in your main table. Then your relationship would be defined as:

                   

                  MainTable::cAvailable = Stocks::Status

                   

                  I'm unsure of which method you are refering to here with mentioning calculation field  and relationship in the same solution. Anyhow I would like to create the value list using a calculation field but I would also like to know how to create a TO wich is a subset of my original table for other uses within my database.

                   

                  So far I have created a calculation field cAvailable and have set this to contain the value "Available" if StockLevel > 0. I have tested this and it is indeed empty if stock level is 0 or contains "Available" if there are any items in stock.

                   

                  Could you explain a little more please.

                   

                  Sorry for being so confused.

                   

                  Thanks again for your help Phil

                   

                  • 6. Re: Showing only records that meet a condition in value lists
                    philmodjunk

                    So far I have created a calculation field cAvailable and have set this to contain the value "Available" if StockLevel > 0. I have tested this and it is indeed empty if stock level is 0 or contains "Available" if there are any items in stock.

                    That's new information not given in your original post. You said in your first post: "only on records where the status is set to available and omit those whose status is set to unavailable." I understood that to mean you had a field named status that contained the text "available" or "Unavailable". Not quite what you are describing now.

                     

                    What I was describing is a field, cAvailable that simply has the text "Available" for all records in your main table. It is intended to be a constant that would match to any records in the related table that have the value "Available". That won't work for what you now describe.

                     

                    I need to know how you've structured your tables and how you track stock levels with the stocks table before I proceed with a different suggestion. Do you have one stock record for each item with a simple number field recording the current stock level or do you use a different approach?

                     

                     

                     

                     

                    • 7. Re: Showing only records that meet a condition in value lists
                      Frinholp

                      Sorry Phil for the confusion. I tried to simplify my situation which seems to have caused a problem.

                       

                      To answer your question:

                       

                       Do you have one stock record for each item with a simple number field recording the current stock level or do you use a different approach?

                       

                      No, I hold the the stock items in a table where the current stock level is a calculated field.. It is calculated by subracting the quantity purchased from the quantity ordered.. In the stock table I have set a field by calculation whose value is "Available" if the calculated quantity is > 0.

                       

                      I have attached a jpeg of my relationships graph and I have also attached a snapshot of my tables. "Artworks" are my stock items.

                       

                      http://img168.imageshack.us/img168/8595/tablese.jpg
                      http://img706.imageshack.us/img706/3240/inventoryn.jpg

                       

                      Hope this clarifies the matter.

                       

                      Cheers again.

                       

                      Lee

                       

                      Edited as incorrect image uploaded 

                      • 8. Re: Showing only records that meet a condition in value lists
                        Frinholp

                        Sorry forgot the images

                         

                        Oops!

                        • 9. Re: Showing only records that meet a condition in value lists
                          philmodjunk

                          I'm afraid I'm still confused. You've posted information about a "stock" table, but there's no stock data source table or table occurrence in the two images to which you've linked your post.

                           

                          I see Orders and OrderLines as data source tables and Two Table Occurrences for OrderLines, OrderLines and ItemsSold.

                           

                          Do you track stock levels in the OrderLines table?

                           

                          If so, you could have multiple records for the same item (Each from a different order) and would use a summary field to compute the balance on hand. That's how I would do this, (I've called this an Inventory Log in other threads in this forum), but it also complicates the process of defining the conditional value list you want as you can't reference a summary field (or a calculation based on a summary field) for your value list. There's a way you can script something that will serve, but let's be sure I am interpreting your database design first.

                          • 10. Re: Showing only records that meet a condition in value lists
                            Frinholp

                            ArtReOrders and ArtRerderLines are also data source tables and there are two Table Occurrences for ArtReOrderLines, ArtReOrderLines and ItemsPurcased. This is basically stock I have ordered in, whereas Orders is stock gone out.

                             

                            Artworks is my inventory. In the artworks table I have a cStockLevel field. Its calculation is 0 + Sum ( ItemsPurchased::QtyReceived ) - Sum ( ItemsSold::Qty ).

                             

                            So to sumerise, I know the quantity of an Artwork in stock by subtracting the total amount ever sold (Orders) from the total amount ever stocked (ArttReOrders)  which is held in a calculation field in the Artworks (Inventory) table. I have a field that is set to Available if cStockLevel > 0.

                             

                            Hope this makes things clearer. I know my naming of TO's is not the best, sorry.

                             

                            Lee

                            • 11. Re: Showing only records that meet a condition in value lists
                              philmodjunk

                              Thanks, that clarifies things and identifies the main problem.

                               

                              By definition, Sum ( ItemsPurchased::QtyReceived ) - Sum ( ItemsSold::Qty ) is an unstored calculation and any calulation fields you set up that refer to this field will also be unstored. Unstored fields cannot be used as source of values for a value list and can't be used as a match field on the "child" side of a relationship and this prevents us from setting up a relationship that supports the use of a typical conditional value list.

                               

                              If you are using Filemaker 11, you could display your values in a portal and set a portal filter to exclude records where this field = 0. You could then set the portal fields up as buttons with a script to add the item to your order if clicked.

                               

                              With older versions of filemaker, you'd need to set up a stored, indexed number field that displays the same value as cStockLevel. The trick here, is you have to identify every action that might alter the stock level and use a script trigger to automatically update the number field to match cStockLevel with a script such as:

                               

                              Set Field [Artworks::StockLevel ; Artworks::cStockLevel]

                               

                              That requires careful layout design on your part, but once done you can use StockLevel in place of cStockLevel to get a conditional value list to work or to set up a portal that displays only records where the item is in stock.

                              • 12. Re: Showing only records that meet a condition in value lists
                                Frinholp

                                Thanks for the solution Phil.

                                 

                                Afraid I'm only using Filemaker 10 Pro Advanced for development but my auntie I'm developing the solution for has purchased Filemaker Pro 11. Which function is it that I can't use in version 10? I know I can set a field as a button as I have tried that before.

                                 

                                I can see that it would take some very very careful design to implement solution 2 but hey if that's what I have to do......plenty of testing too to make sure I haven't missed a case!

                                 

                                The way I implemented the inventory system I took from the Filemaker Bible. Also I have seen this type of system used in many other examples too. What is the benefit of using such system rather than just incrementing a field in the "Artwork Table" directly? I'm assuming it has something to do with multiple users trying to modify that record at the same time?

                                 

                                Being a little cheeky here, can you think of a better way to implement my stock control?

                                • 13. Re: Showing only records that meet a condition in value lists
                                  philmodjunk

                                  I see nothing wrong with the basic design you are using, it's just that this design prevents you from using the typical approach for a conditional value list because the totals will be unstored. The method I prefer, an inventory log, has the same limitation.

                                   

                                  With filemaker 11, you can opt to use a filtered portal in place of a value list, as you can set up a filter expression that only shows those items in the table that have a stock level greater than zero and this does not require a stored value. This option takes up more space on your layout than a drop down, but is easier to set up and you avoid problems with two different stock level fields (one stored as a simple number  for your conditional value list and one that's an unstored calculation) getting out of synch with each other.

                                  • 14. Re: Showing only records that meet a condition in value lists
                                    Frinholp

                                    Thanks for all the advice Phil. Most appreciated.