How to filter a multi level relationship

[ejpvi]

I will try my best to explain this.

I have an external database in sql that I am pulling information from.

This is working exactly how I want it. Where I am running into issues is that I want to be able to filter the results returned by date (which is a variable in one of the tables)

Here is the issue.

In FMP i have a product table.

I have a relationship to an external table called "Items" and is linked by an item #.

Then the external "Items" table Occurence is linked to another external table Occurence called "CMS" by order number (which is in both of those tables, but not my FMP)

When I create a Portal for this with variables from both of these occurrences, all of the necessary variables are returned properly. Except that it returns information for all dates. I want to filter that down by date, so that I can create some summary fields.

I would like to be able to change the criteria between my FMP table, and the "Items" table to be if

Item = item (in the "Items" Occurence)

and

Order Date = Date Range I choose. (in the "CMS" Occurence)

But Order Date, is in the "CMS" table not in "Items".

How could I maintain my link to ITEMS, but only return variables that match the criteria in CMS?

Confusing... Sorry... I hope this makes sense.

Right now my relationship kind of looks like this:

Product Table(FMP) <-> Items (SQL) <-> CMS (SQL)

Any Ideas?

[comment]

I am not sure how the ESS part plays in this. I presume you cannot add a global date field to the external Items table, but couldn't a calculation field = SomeFMPTable::gDate do the job?

Alternatively, you could link to another occurrence of CMS by date, and pull a list of order numbers to be used in filtering the relationship to Items.

[ejpvi]

The 2nd part of this.. which maybe you will have some insight...

If I want to create a summary field, and sub-summary parts of one of those external tables... how would I go about that?

I can't add the summary variables to those external occurrences....so I am not sure how to return all of the values, and then aggregate their values.

[comment]

I prefer someone more experienced with ESS answer this. Perhaps you should create a new thread in the ESS section - it doesn't seem to be "the 2nd part of this".