Perform the find on the orders table and specify the date criteria and the city in the related customer table in one find request. This should produce a set of all orders records with dates that match your date criteria, but limited to orders from customers in the specified city.
If this is not the set of records you want, please describe what would need to change in that set to produce the set of records that you need.
I think you need to find the customers by City, then constrain that found set by omitting the orders more recent to the cutoff date.
Peform Find [customers::city ; CITY]
Constrain Found Set [order::date ; <DATE]
Unfortunately non of the suggestions is producing the resul I want. Let me struggle with that for the rest of the day and if I cannot I will revisit it naxt week after Xmas and update you. Thank you and Merry Xmas
What result are you getting and how does it differe from what you want?
I have been toying more with this issue and I am still stuck. This is my problem I have a layout based on the customer table and I need to produce the report from it because I need to list one line per customer. If I do my find from it it simply shows me all customers who did not have orders after the cutoff date but for those in the found set it is still keeping all orders new and old.
So if I go to another layuot based on erders it does find what I want but I cannot print the report from it because it will list for me all orders (not only the last one prior to the cutoff date) for each customer.
The problem does not stop here because after I accomplish this I need another version of the report to show the last payment date in addition, which would be kept in a third table.
So what can I do?
I am really missing two feature in filemaker which I used to use a lot in other packages which are
1- the ability to filter each of the related tables indepandently, and then.
2- the ability to specify dynamic relationships which you may modify during the scripting.
If these exist and I am not aware of them please let me know.
Is this what you want?
Customer Name 1 | Fields from most recent Order record
Customer Name 2 | Fields from most recent Order record
and filtered to only list those customers that are from a specified city (field in Customer table) and that place an oder prior to a specified date (field in orderst table).
Sounds like you can get the list of customer names, but the order fields are from the most recent order instead of the most recent order prior to the specified date. Is that correct?
I'd use a layout based on the customer table for this, but then would use either a filetered portal or a portal with a filtered relationship to display data from the correct Order record.
A filtered relationship might look like this:
Customers::CustomerID = DateSpecifiedOrders::CustomerID AND
Customers::gSelectedDate > DateSpecifiedOrders::OrderDate
gSelectedDate is a date field defined in Customers with global storage specified so you can select one date for all customers records.
DateSpecifiedOrders is a new occurrence of your Orders table. You can create additional table occurrences of Orders by clicking it in Manage | database |relationships, then clicking the duplicate button (two green plus signs).
A filtered portal would use your original relationship:
Customers::CustomerID = Orders::CustomerID
But use a portal filter expression such as:
Customers::gSelectedDate > Orders::OrderDate
Ok thank you I will try that and let you know.
Is your idea of filtered portals an answer for what I expressed about the desire of filtering a related table independently.?
And how about the capability to define a relationship dunamically, does it exisr?
You cannot define relationships dynamically in FileMaker, but there are methods that can achieve most the results you would want to see from such a use in a SQL based system.Many such "on the fly" queries can be done in FileMaker with a scripted Find that uses data specified in global fields by the user. Filtered portals can containe a complex calcualtion that evaluates/filters differently depending on values set by the user or a script in fields or vairables referenced in the calcualtion.
I described two methods as each has trade offs. The filtered relationship requires adding a table occurrence, but may work better with larger sets of data than a filtered portal. You can test both options with your database and see which works best for you.