Set related field to "Any Value"

[TheLagosChap]

Hi,

I have an "invoice line" table which has, amongst others, "Item Code" and "Shop Code" fields.

I use another table "Invoice Line Summary" to group my invoice lines per Item Code and Shop Code.

So, Invoice Line is linked to Invoice Line Summary via Item Code and Shop Code (with "=" operator).

Now I would like to use my "Invoice Line Summary" table to group the Invoice Lines for ALL Shops.

So, basically, I would like the "Shop Code" field in my "Invoice Line Summary" to contain "any value".

I know I can put "all values" in the Shop Code field (separated by return) and it works fine.

But I was hoping I could put some kind of wild card instead to catch related records with any value (a bit like using the "X" relationship operator).

BTW, controling the relationship operator by script (changing it from "=" to "X" and back) would help too but I didn't find a way to do this either.

Any hint appreciated.

Thanks.

[Søren Dyhr]

I'm a little confused here are we talking summaries as in summary reports or just aggregates??

But I would probably be duping the TO, remember the RD is not a ERD - since this it's two distinctive documents, have a layout for each or facilitate your solution with a tabbed layout, where each tab show their aspect respectively. One tab with both criterias working and one with a single relation pending. Each portal can have it's own sort order if nessersary.

--sd

[TheLagosChap]

Hi sd,

I'm talking about relationship between 2 tables (for instance to display a portal) rather than summary in a report layout. Sorry about the confusion. Actually, I want to "simulate", in a portal, what the summary function can do in report layouts.

Not sure what you mean by "duping the TO" but, if I understand well, you suggest to use 2 relationship (and 2 layouts, 2 portals).

It's not exactly what I'm looking for. Actually, I'm looking for a shortcut to avoid creating 2 instances of the same table and 2 different relationships, one with one criteria "Item Code = Item Code" and "Shop code = Shop code" and another relationship with only "Item Code = Item Code" (allowing to view the related records for all Shops).

This would work very well but, creating one table view/instance and one relationship is "too much work". In fact, for the simple example I described it would be fine. But I'm actually looking for something I can implement for a combination of at least 4 criteria.

Using the "natural" solution you propose, it would force me to create plenty views on the same table with each having its own "specialised" relationship.

Let's say for instance I have 4 criteria fields (A,B,C and D), I would need to create one relationship for each combination, like:

1. A=A and B=B and C=C and D=D

2. A=A and B=B and C=C

3. A=A and B=B and D=D

4. A=A and B=B

5. A=A

6. B=B and C=C and D=D

7. B=B and D=D

8. B=B

etc

I hope I can make one relationship that includes all fields and, dynamically, turn some "on" or "off".

Like I said, this could be achieved by putting some kind of "catch all" value in some of the fields or by dynamically changing the relationship operator (from "=" to "X" and vice versa).

So, in my example, I would have only one relationship (A=A and B=B and C=C and D=D) but would put some "wildcards" in the fields I want to "turn off", like:

1. A=A and B=B and C=C and D=D

2. A=A and B=B and C=C and D = "any"

3. A=A and B=B and C = "any" and D = D

4. A=A and B=B and C = "any" and D = "any"

etc

or, dynamically change the relationship to, for instance:

1. A=A and B=B and C=C and D=D

2. A=A and B=B and C=C and D x D

3. A=A and B=B and C x C and D = D

4. A=A and B=B and C x C and D x D

etc

I hope this is more clear.

Thx.

[Søren Dyhr]

My answer havn't changed much since then, still is the solution to have a TOG for each of the 4 lines here:

1. A=A and B=B and C=C and D=D

2. A=A and B=B and C=C and D x D

3. A=A and B=B and C x C and D = D

4. A=A and B=B and C x C and D x D

...but where all the occurances of cartisian product are deleted so it becomes:

1. A=A and B=B and C=C and D=D

2. A=A and B=B and C=C

3. A=A and B=B and D = D

4. A=A and B=B

Each line have it's own either layout or tab.

Using the "natural" solution you propose, it would force me to create plenty views on the same table with each having its own "specialised" relationship.

Yes indeed! The alternative is to use ValuelistItems( like it's used in this video:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

Yet another way to deal with it, is to utilize real Summaries, like it's done here:

http://www.kevinfrank.com/download/kf-fast-summary.zip

...and then have a button for each aspect you wish to show.

--sd

[comment]

Sounds very similar to these two threads:

http://fmforums.com/forum/showtopic.php?tid/171912

http://fmforums.com/forum/showtopic.php?tid/172518/

[TheLagosChap]

I've seen a lot of interesting hints in all the links provided.

I now need to spend the time and analyse but it really looks like one of them will do.

Well done - Thanks!