Couting Through Relationships

[x14607]

The question involves 3 tables:

Products, Resources and a Join table. Products and Resources each have an ID field of their own, both of which appear in the join table to link them.

I have a portal on the Products table that shows which resources are required to product that product (The portal is linked to the Join table, so that when productID matches, the Resource will appear in the portal)...

I've come up with a script to enter resources into the portal. Since it would be pointless to use a resource twice (there's a quantity field), I need to somehow COUNT how many times this resource exists via this product already. If that count is greater than 0, I know it would be a repeat.

What do I count? And where?

I've been able to count various things within the databse already, but this one I can't seem to get. In fact, the whole concept of context and calculating via relationships seems to have missed me somewhow.

I understand the concept in my head, but applying it seems hard for me to grasp. I find myself not knowing what should calculate from where, and how many tables it should go through, and if it has to 'stop' anywhere along the way...

If anyone knows of a thorough and easy to grasp explanation of the whole concept somewhere, I'd be interested. I wouldn't have so many of these questions then =)

Thanks for any help on this.

[Ender]

It sounds like you want to see when a particular Resource line item has already been used by the same Product. To do this I think you will need a self join on the join table (add a second Table Occurance of the join table and set the relationship to be by ProductID AND ResourceID.) This is done in the join table because only the join table knows its ProductID-ResourceID pair to compare with to see if there are multiple entries. Now use a calculation in the join table to flag the line as having multiples:

Flag (calculation, text result) = Case(Count(SelfJoin::ResourceID) > 1; "Multiple Entries")

You will need to click out of the portal for the calc to update, however.

[RalphL]

I have taken a different approach. I make a value list of unused Resources. I Have 3 Value lists: All, Used & Unused.

All is from the Serial Number in Resource.

Used is from the Resource Serial Number in the join table.

I have a field used = "0

[x14607]

Both concepts will work in different places in my database. For this particular instance though I think the value list concept is best. I may seem a bit slow here, but I need some elaboration on implementing the idea. I think I understand the All and Used lists, but am unclear on the Unused value list, which is really the only one I'll need to show in this case.

I'd love to implement this one if there is any way you could clarify.

Thanks for both ideas. The first clears up a lot for me, and helps me to understand how to count in my other tables, and in this case the second is just the right fit.

I appreciate all of the help...

[x14607]

I've tried to implement the last idea mentioned here by creating:

* the "Used" value list based on the join table

* the Used field in the Products table

* another Resources table occurence with the 'not equal to' relationship between the resource serial# and Used field(from Prodcuts)

*the value list unused, containing all of the Resource serial numbers from this new table occurence

Now this value list is in a portal (portal based on the join table of course), but I've tried putting my value list in a portal row and outside of a portal row and I still get a value list with all of the resources, used or not.

Anyone know what I'm doing wrong?

This sounds like a perfect idea, and I'd love to get it working.

Thanks in advance for any and all help.

[Søren Dyhr]

We had it up here in this thread:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=133914

--sd

[x14607]

Thanks for the reply... it took awhile but I figured out how to relate this to my database (I'm still new to this whole relational concept)...

Still one problem though...

When I start with a product that currently has no Resources associated with it yet, (in other words the Used field would be empty, because no values have yet been used)... My value list displays <No Values Defined>

Again I'm sure there is a very simple thing I am missing here, but as of yet I'm not sure what it is.

Any ideas?

Thanks in advance

[Søren Dyhr]

When I start with a product that currently has no Resources associated with it yet, (in other words the Used field would be empty, because no values have yet been used)... My value list displays <No Values Defined>

Ah you didn't take notice when Ralph wrote:

I have a field used = "0

[x14607]

Sorry to be a pain here, but that's exactly what I have defined for my USED field, which is in the Products table:

"0

[Søren Dyhr]

This is a long shot, perhaps isn't your field a textfield??? ...or you havn't used Evaluate( with the escape chars???

--sd

[x14607]

Thanks for the reply...

It is a text field, but as far as Evaluate goes - I tried putting the whole thing into Evaluate, but this made the dwindling value list not work at all... it looked liek this:

Evaluate ("

[Søren Dyhr]

Evaluate ("

[-Queue-]

If you want the pilcrow in the result, you'll need to escape it also.

Evaluate( "

[x14607]

This wasn't the problem, I was using the other concept mentioned in the previous thread, to referesh the results using the refresh window with the cache option...

By tinkering around with things I found the problem, and I hope this helps anyone else who is a newbie trying to implement this dwindling value list concept (which is a very great thing!)

My value list was defined to use the IDs from the UNUSED table (an occurence of Resources, showing values that do NOT match the USED calculation....which is correct) BUT at first was set to show realted values from the JOIN table, which worked, except when there were no values. When I changed this to show only related values from the PRODUCT table, voila!

I was thinking that since only the Join table knows when both a product and resource are being used together, that was the table to rely on. But since the relationship there has already been established via the UNUSED table occurence.

I'm sure that sounded very confusing but I'm starting tgo comprehend this now.

Any further tips? Key points I should have learned from this experience?

Thanks again though for all of your efforts everyone. This forum is extremely helpful!