Can you show a screen shot of your relationship graph and the layouts in question (both browse mode and layout mode)? Also can you describe the work flow as it relates to the tables.
Basically, every payment should be a record on a payment table, which should be a join table between products and sales (customers).
Then searching would be standard.
There are plenty of resources. If I remember correctly, the book-FileMaker Pro 12: The Missing Manual has a simple invoicing solution that takes into account partial payments. I don't know which version of FM you are using or if it's a newer version of the book if the same sample file is there. But usually if you go the website, you can download the sample files.
Searching for a range is a common find technique that can be done with scripting or by a user in Find Mode
Finds are an essential technique and deserves every bit of effort and time it takes to master.
I would suggest a dashboard table and layout. The table has only one record and the fields are 3 globals, for now: gDateBegin, gDateEnd and gAppleTypeID.
There are 2 relationships from dashboard to payments, one is based only on the 2 gDates, the other one includes the appleType.
You have 2 portals on the dashboard, each based upon one of the 2 relationships.
You completely hide one of the two depending on gAppleTypeID being empty or not.
Thanks very much everyone. I think we've bitten off a bit more than we can chew with this question as we're falling down on even inputting the data in the first place!!
We've bought the missing manual for Filemaker Pro Advanced 14 (the version we have) but its hard to find the right sections if we don't know the terminology!
If it is ok to stick with this thread, our new question is:
We're inputting sales for each apple month on month, and have created a list view layout to see all apples in alphabetical order (we've got hundreds of differently names apples so a dropdown is not an option) to find them easily and quickly. It makes sense to input a new date and payment amount in this view, however we're only seeing the very first payment and date for each apple, and if we change it it changes the original record instead of adding a new one.
We have successfully added records via a portal to a payment table, but having to go into each individual record to do it is too time consuming, especially as the apples were not added to the database in alphabetical order (and is ongoing, so new apples are added all the time).
Have we set up the link to the payment field incorrectly?
Thanks very much,