10 Replies Latest reply on Aug 22, 2011 11:41 AM by BrianClemens

    Help with a drop down list query/script?

    BrianClemens

      Title

      Help with a drop down list query/script?

      Post

      I am working on a dealer/product database.

       

      I have (60) dealers.  For each dealer there is a "shipped Date" and a "sold date" for cars, lets say.

      For each dealer I want to show a report...

      In stock: shipped date is there, but sold date is empty, below that (sub report I assume)

      Sold: Shipped date is there & sold date is there.  I think I can get that far with queries.

      My question is, I'd like to have a drop down box somewhere so I can select each dealer and their corresponding data comes up.  Can I do a drop down box script? where when I select dealer "ABC" it shows the data that containts their name?  And then If I select another dealer the data changes?  I'd rather not do a layout for each dealer, I just want the data to change.

        • 1. Re: Help with a drop down list query/script?
          philmodjunk

          See my response to your other post: http://forums.filemaker.com/posts/2208f8e43d#157120

          It provides an example script that can also be adapted to this use. You can use a script trigger such as OnObjectModfy (pop up menus) or OnObjectSave ( Drop down lists) to perform such scripts if you want selecting a value to automatically perform the find.

          • 2. Re: Help with a drop down list query/script?
            BrianClemens

            I've got the "current month" working as a result of your help.

            I am trying to adapt the script to work for this as well.  I have (2) issues. The layout & the script.

            I would like to show (grouped) for each dealer their "Inventory" (soldDate is empty) at the top and "Sold" (soldDate has a value) at the bottom.  I've started to set up the report, but I don't see a way to "group" based on whether a cell is blank or has data.

             

            I can get the script to bring up a dialog box, and it asks me to fill in the Dealer Name.  I have another table that lists the dealer information...I'd like pul the dealer names from that table, but I can't see where the drop box lets me pull from that data.  It only lets me use a "Value List".   

            • 3. Re: Help with a drop down list query/script?
              philmodjunk

              A value list can be set up to list values from a field in a table if you use the specify field option in Manage | valuelists. So your value list can list the dealer names as entered in one of your database tables.

              One very simple way to group your data as you describe is to sort your records after you find them by sorting first by dealer name, then by the SoldDate field. If you sort by SoldDate in ascending order, the records with empty date fields will be listed first. I suspect, though that you want to group these records under two subheadings, one records with an empty field and one for those where the field is not empty. Accomplishing that requires a calculation field with one value if the field has a date and a different value if it does not.

              cSold could be defined as:

              SoldDate > 0

              With "Number" as its return type.

              It will return 1 if Soldate has a value and be empty if SoldDate does not.

              Now you can sort by cSold to group your records and a sub summary part set up with "when sorted by cSold" can be used to produce a sub header for each of these groups.

              • 4. Re: Help with a drop down list query/script?
                BrianClemens

                I've got the report working properly.  Is there a way to put a title in for each suv-summary?

                It is grouping by cSold.  I'd like to put a title on the first group "Inventory" and a title for the next group "Sold".

                When I put a title in the "sub-summary section, it shows up the same on both groups.

                • 5. Re: Help with a drop down list query/script?
                  philmodjunk

                  You can define a calculation field set to return text that looks like this:

                  If ( cSold ; "Sold" ; "Inventory )

                  Then put this field in your sub summary part.

                  You can also put both labels on top of each other as layout text. (Click the T tool while in layout mode.) Then use conditional formatting to hide each text object depending on the value of cSold.

                  For "Inventory", you can use the expression: cSold and specify a font side of 500. This size setting will make the text invisible when the records in the sub summary part are for cars that were sold.

                  Use: Not cSold for "Sold" and specify a font size of 500 for it as well and then only one layout text object will be visible in each sub heading.

                  • 6. Re: Help with a drop down list query/script?
                    BrianClemens

                    I can sort cSold if the calculation generates a number...but when I switch that to generate "text"  (If (sold) > 0, "Sold", "Inventory") it does not want to group with that text.  It groups them all together??

                    • 7. Re: Help with a drop down list query/script?
                      philmodjunk

                      You need two different fields. cSold should be left as originally defined. You then add a new calculattion field set to return text that is designe to provide the "Inventory" or "Sold" text. (Or you can use the second option with the layout text objects and conditional formats in place of this additional calculation field.)

                      • 8. Re: Help with a drop down list query/script?
                        BrianClemens

                        Is there a way to make that subsummary title leading? Instead of trailing?

                        • 9. Re: Help with a drop down list query/script?
                          philmodjunk

                          You can do either or both. That's where the "print below" or "Print above" option comes in when you create the part. After you've created such a layout part, you can drag them up or down in the Part setup dialog to change this position relative to the other parts.

                          • 10. Re: Help with a drop down list query/script?
                            BrianClemens

                            thank you.  I swtiched everything to an "if statement" to get (3) results (On Order, Inventory & Sold), for some reason I had a calculation error so it was not showing the text properly.  I fixed that and everything is woking great.