I just wonder how to realize a form that shows all "issues" of a "magazine title" (like "Newsweek" -> No 1, 2, 3 ...). I have these two tables and a m:n-relation-table.
I think you should elaborate on that last bit.
Well, what I have:
Imagine a print publishing company. People can order former and current issues of the different magazins. Now I want to show a layout of a customer and his purchases. Therefore a "customer" layout shall show all issues of all magazines that are in a table "issues" while each issue belongs to a "magazine "table magazine". In a m:n-relation "purchases" I save the field "ID customer" and "ID issue".
Now I wonder how I can display all "issues" in a "customer" layout while the purchased issues are ticked and all others are not.
Does this make it more understandable?
I have these two tables and a m:n-relation-table.
It seems you're missing a table (or didn't mention it). What you need is:
Magazines --< Issues (magazineFK) --< IssuesPurchased (issueFK, customerFK) >-- Customers
Create a new TO of Issues and connect it in a Cartesian relationship to Customers, to display all issues in a portal; use Conditional Formatting to mark the purchased issues like so:
not isempty ( Filtervalues ( List ( IssuesPurchased::issueFK ) ; IssuesCartesian::issuePK ) )
which means a record in the list of all issues will be formatted if it is also in the list of IssuesPurchased (for this customer).
Note that you could use the same expression as basis for a portal filter; combined with a global field and a script, you could switch the portal display between all, purchased and non-purchased issues.
Also, this works for all issues of all magazines, in case you have several. If you want to focus on purchases for one or more specific magazines, things get a bit more complicated.
Great, I now understand the Cartesian relationship and that works fine.Also the conditional formating. Allow me two further questions:
1. how can I show the "amount of purchased issue", a field in "IssuesPurchased". I tried to show the field but only the value of the first of more data entry in the relation table is shown.
2. how do I switch the portal display between all, purchased and non-purchased issues. I need a global value in "Customers" I assume. But then?
Something to play around with that should answer your questions (if you dig deep enough, that is …); using only global variables, CF, scripts and a few additional TOs.
Note that you could also use global or “normal” fields to store the misc. selection IDs and lists, with the caveats that:
• global fields can store settings per user, but they apply to all records
• “normal” fields can store settings per record, but they apply to all users (so Jane can (and will) inadvertently overwrite Joe's selection; not catastrophic, but not a good UI, either)
Using dynamically generated, recordID-tagged $$vars has AFAICS all the pros and none of the cons (except they're a bit harder to set up and debug than fields).
So if yours is a single-user solution and you don't want to fiddle around with Let() and Evaluate() and/or Custom Functions, by all means go for normal fields.
Have fun while digging
MagazineIssues_eos.fmp12.zip 16.9 K