Relationship Difficulty

[ldipeso]

I'm new to FMP, and I'm not sure how to accomplish something:

I have a Contacts table. It's related to an Orders Table via a contact ID. The Orders table is then related to a Line Items table via the Order ID. In the Line Items Table is a Webinar ID. This is related to a Webinars table.

So:

Contacts->Orders->Line Items<-Webinars

In the Orders table is a field called "status". The Order status can be "Completed", "Pending", or "Failed".

What I'd like to do is when viewing the record of a Webinar, show a list of contacts who have ordered that Webinar and completed that Order, as well as show a separate list of contacts who have ordered, but the order was not completed (ie its status was Pending or Failed).

So far, I've been able to get it to show a list of contacts who have ordered, but I can't get it to filter based on the status of the order.

[Søren Dyhr]

Read this thread, a few more calc'fields and some extra table occurrences, and you're there.

http://www.fmforums.com/forum/showtopic.php?tid/191906/post/275744/hl/tagging/fromsearch/1/#275744

--sd

[ldipeso]

Søren,

What I've been able to do so far is in a Webinar record, have a portal showing all completed orders and a portal showing all incomplete orders.

However, it doesn't quite work right. Here's what happens

If that particular Webinar was never ordered (so it's not in the Line Items table), the portals correctly show no records, both in the "completed orders" portal and in the "incomplete orders" portals.

If, however, that Webinar was ordered, then the "completed orders" portal lists every completed order, and the "incomplete orders" portal lists every incomplete order.

How do I get it to only show orders relevant to that particular Webinar?

Here's how my tables are setup:

Contacts---< Orders ----< Line Items >---- Webinars

Orders |----< Complete Orders

Orders |----< Incomplete Orders

The relationship between Orders and Complete Orders is a selfjoin: I store the text "Complete" in a field called gComplete. I then draw a relationship between gComplete and the Status field of Complete Orders: If "Complete Orders" Status field is = "Complete".

For Incomplete Orders, I do the same, but use a != relationship (so If Incomplete Orders' Status field is != "Complete").

I hope this makes some sense.

Edited January 22, 2008 by Guest

[Søren Dyhr]

I would not use a global field, but instead 3 calc'fields like these:

Completed Calculation (Number) from Orders, = Case ( Status = "Completed";OrderID ) 

Pending Calculation (Number) from Orders, = Case ( Status = "Pending";OrderID ) 

Failed Calculation (Number) from Orders, = Case ( Status = "Failed";OrderID ) 

.....and then build the RG shown above. Where the yellow boxes is the actual layouts in the solutions graph. The purple ones are then the portals in the layout, the green ones are there to facilitate the filtering.

If you need to see the portals disregarding the record your cursor is in, is it the same graph, just with the left most relation in each anchor/bouy that need to change into a cartesian relation instead of the equal ditto, again is not a single global field in action!!!

--sd

[ldipeso]

Thank you Søren!

Your advice not only solved the problem, but finally helped me to understand how to wrap my mind around FileMaker.

I'm primarily a MySQL developer, so I didn't quite grasp the concept of a "TO" or how to filter things in FM. I still don't entirely understand it, but I'm starting to grasp things.

[Søren Dyhr]

I still don't entirely understand it, but I'm starting to grasp things.

Yes! You seemed versed enough to be a false beginner with database concepts. But what you need to read now is this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

...and watch this movie:

http://www.filemakermagazine.com/secured/541/GraphRules_full.mov

But then should by now have cautioned from casual use of globals, on several occasions, havn't you? - I can't be the first who've done so?

--sd

[ldipeso]

Søren,

I read the article -- an incredible eye-opener! I think I understand the relationships graph now and what "table occurrences" are. Coming from MySQL, I thought I was dealing with a standard ER, but that is not the case at all.

Unfortunately, the video you linked to requires that I subscribe to a magazine, so I am not able to view it.

Nonetheless, the article by itself is worth its weight in gold.

Thank you again for all of your help! If you know of any other resources for learning about how FileMaker works, they would be much appreciated.

I will also avoid the use of globals -- I had a bad feeling about them in any event, as they seemed "brittle", or static, and prone to breaking things without a lot of maintenance.

Edited January 28, 2008 by Guest

[ldipeso]

Søren,

Firstly: I just want to thank you again for all of the help you've given so far.

I do have another question for you, if you wouldn't mind me asking. I've set up the tables as you suggested for the Webinars layout.

However, I'd like to set things up so that a person can not only purchase an individual Webinar, but purchase a subscription to a Webinar Series.

So, Webinars would belong to a Webinar Series (e.g., the Webinar "How to Make Waffles" might belong to the "Breakfast Series"). A person could purchase a 1 month, or 6 month, or 1 year long subscription to the "Breakfast Series" and have access to any Webinars in that Series that are scheduled to happen during their subscription period.

What I would like to have is a portal on the Webinars layout listing all the contacts who have access to that webinar because they have purchased a subscription to the Webinar Series to which that Webinar belongs.

For example, if "Bob" purchased a 1 month subscription to the "Breakfast Series", and the "How to Make Waffles" Webinar was scheduled sometime during Bob's subscription, I'd like to show Bob's information on the "How to Make Waffles" record.

I'm just not sure how to accomplish this. I know this may be confusing, and if so, I apologize. I've attached a screen shot of what I have so far -- perhaps it will help you to make sense of what I am trying to accomplish.

Edited January 29, 2008 by Guest

[Søren Dyhr]

If every subscription has a start and ending date, would I use:

http://www.filemaker.com/help/FunctionsRef-216.html

...to filter in all subscriptions made by the person, either by Ugo's method or a straight forward multi-criteria, somewhat in the vicinity of:

http://www.newcenturydata.com/downloads/filter.zip

--sd

[ldipeso]

If every subscription has a start and ending date, would I use:

http://www.filemaker.com/help/FunctionsRef-216.html

...to filter in all subscriptions made by the person, either by Ugo's method or a straight forward multi-criteria, somewhat in the vicinity of:

http://www.newcenturydata.com/downloads/filter.zip

--sd

Søren:

I'm still not getting this to work properly. I've set up the Webinars to be a part of one Webinar Group or another. When viewing a Webinar record, I can get it to show a portal with all current subscriptions that are a part of that Webinar Group.

However, what I'd like is 3 separate portals: one showing subscriptions where the order has been completed, one showing subscriptions where the order is still pending, and one showing subscriptions where the order has failed (similar to what you helped me with above).

I've tried all kinds of relationships and long strings of TOs, but can't get it to work properly.

What I have is:

Webinars>---( Webinars::Webinar Series ID = Webinar Subscriptions::Webinar Series ID )-------Contacts

The Date Filter is a second TO of the Webinar Subscriptions table that is used to filter for current subscriptions only. I do that by using a calc field called "Current Subscription" that checks to see if the subscription is current.

The code for the calc field is below:

= Case( Get(CurrentTimeStamp) >= Start Date and Get(CurrentTimeStamp) < End Date; Subscription ID)

I join Date Filter and Webinar Subscriptions by checking if the Subscription ID of Webinar Subscriptions = Current Subscription

[Søren Dyhr]

Before going into this, should it be noted that you compare timestamps with dates ... if you compare them directly will their numerical values be taken:

Current date is: 733071

....while the timestamp some minutes ago was: 63337285038

--sd

[ldipeso]

Søren,

Actually, "Start Date" and "End Date" are timestamps. Probably not the most clearly named.

Thanks,

Lucian

Edited January 30, 2008 by Guest

[Søren Dyhr]

Ok! Take a look at the attached template, does it make sense??

--sd

bookings.zip