In filemaker you find groups of records by "Performing Finds" instead of pulling up a wizard that builds a SQL query for you. For simple data searches this is much easier, especially since the find is performed on the layout (in access this would be a "report" or "form"), not in a separate window that pulls up a table view of the data when you're done.
Enter find mode
Type criteria in each field as needed
Click Perform Find
Filemaker then pulls up all matching records for you to click or scroll through.
To find all records dated 5/1/2009...5/31/2009, (MMDDYYYY format):
Enter find mode and type in the above date range exactly (date1...date2), or just enter 5/2009
click perform find and you've got them.
For item 2, you'll need to be more specific. The method used will depend on how you are recording payments and billing. Do you have a separate table recording payments? Can a given invoice record multliple payments or just one?
Ok. Got that !!! Now for number # 1,
I am trying to understand. But, when i do a FIND i only get the items for the particular client (good !) but it will not show me continuosly all the details of the subsequent orders. Only the first line or the first item of the order. I tried using a portal but still it will not show the 30 items accumulated in all the orders.
The find process works, but i am not able to show all the items for "Bob" I want to be able to summarize how much "Bob" has spent on all his purchases.
For number 2, i am using a separate table called "payment registry" where i include the clientid, the account where i deposited the money (collected), the purchase order which is related to ( order and order detail ), total amount paid, date of the deposit and some other details.
I would like to be able to say "order #1" has been cleared because all the money has been collected.
show all the money collected and the oustanding balance.
Basically, i want to be able to have a report that shows me : all the items and the item the person has ordered and all the money collected towards the various orders.
However, if i do "send records to EXCEL" it will send me all the data correctly. ( it is strange in filemaker )
thanks again for your help
"I am trying to understand. But, when i do a FIND i only get the items for the particular client (good !) but it will not show me continuosly all the details of the subsequent orders. Only the first line or the first item of the order. I tried using a portal but still it will not show the 30 items accumulated in all the orders.
The find process works, but i am not able to show all the items for "Bob" I want to be able to summarize how much "Bob" has spent on all his purchases."
In any database, the devil is in the details and not all the details to your database design are immediately apparent. Your original post specifed simply searching for all records dated within a given month for example.
First issue, what table are you searching with your find request? Do you want to see a single list of all "Bob's" order items for all invoices posted within a given date range or month? Or do you want to see a list of all Bob's invoice records for a given time period? Or a found set of his actual invoice records?
The precise details you go through to set this up depend on what you want to see when you're done.
I'm guessing the first option is what you want.
Set up a list view layout based on your order items table. You can place customer fields such as a customer name and Invoice dates from the related tables in the header or a sub-summary part. You can create a summary field to total all the purchases as a field in your Order Items table and place this field in either a footer or trailing grand summary of this layout.
Select this layout, enter find mode and enter your search criteria. You can enter criteria such as an invoice date or date range in fields from a related table.
Perform the find. Sort the found set if needed (and it will be if you have sub-summary report parts.) and you're done.
Let's see if you can get that to work to your satisfaction first. The second issue will likely be resolved through proper relationship design and the creation of a few calculation fields to sum up total related payments and compare that amount to total related invoice charges.