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.
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".
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.
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.
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.
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??
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.)
Is there a way to make that subsummary title leading? Instead of trailing?
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.
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.