Relationship problem

[tv_kid]

Hello everyone. I hope you can help with a relationship problem that is driving me up the wall.

Basically, I have one table ('calendar') that is linked to a 'bookings' table. This 'bookings' table is also linked to a 'people' table. I am using it to 'book' these people on jobs on different days. However, I want to be able to see at a glance the people who have no bookings associated with them on any given day. I can do this via scripts, but it's cumbersome. I'm sure it must be possible via relationships.

My original thought was this:

- In the 'people' table define 7 calculation fields that referenced the 7 dates in a week from the 'calendar' table (which dynamically change as the week changes).

- From the 'people' table establish 7 relationships to the 'bookings' table (i.e calc_date1 to booking_date etc).

- Back in the 'people' table define 7 calculation fields that are simply the found count in the 'bookings' table via each relationship.

- Establish relationships from 'calendar' table to 'people' table - global constant field in 'calendar' (with value = 0) to found count field in 'people'.

I thought that this would then show me the people who had a booking found count of 0 for the selected day.

However, my portals were sadly empty! When I put a single field (not in a portal) on the layout it came up with "Index Missing" so I know I've gone wrong somewhere, but can't figure out where.

Can anyone throw any light on this?

Thanks in advance.

[SlimJim]

I believe the following is the problem:

The clue is in the Index Missing. This is because Found Count cannot be indexed - it uses a related record - and if it cannot be indexed it cannot be used in a relationship because a relationship matches Indexes of fields as opposed to the fields themselves.

To check whether this is the problem try to turn on indexing in your found count field.

[tv_kid]

Yes, trying to turn on indexing brings up an error message. So, any idea of how to get round this problem?

[comment]

See attached.

bookingsVsUnassigned.fp7.zip

[tv_kid]

Thank you! That appears to do exactly what I need. Time to check out the ValueListItems function.

That's really helpful, many thanks again.

[tv_kid]

Ah, I've hit a snag. When changing the dates (gDate1 in your example file) the "Assigned" portal updates, but the "Unnassigned" one doesn't. Any thoughts?

[comment]

Yes, there will usually be a refresh problem when cascading relationships so.

The solution is to always change the calendar's anchor date (gDate1 in my example) by a script that ends with Refresh Window [ Flush cached join results ] script step.

[Søren Dyhr]

Let me suggest following alterations to the keying calc's:

Case ( 1; "a¶" & ValueListItems ( Get (FileName) ; "AssignedIDs" );gDate1)

Case ( 1; "a¶" & ValueListItems ( Get (FileName) ; "AssignedIDs2" );gDate1)

Because a key field only containing a delimiter gives an empty portal for the days lacking any bookings! The Case( statement is there to freshen when the dates are changed!!!

--sd

[comment]

a key field only containing a delimiter gives an empty portal for the days lacking any bookings!

Well, a day with no booking SHOULD show an empty portal. But you have a very interesting point there. The implementation, I think, can be much simpler:

Let ( [

trigger = gDate1

] ;

ValueListItems ( Get (FileName) ; "AssignedIDs" ) & ¶

)

This indeed refreshes immediately. I thought it was a cache problem, but it seems an unstored calc is not as unstored as one would think, and including a trigger can help. Now I will have to re-examine other files with refresh issues, to see which ones can be fixed in the same way.

[Søren Dyhr]

I thought it was a cache problem, but it seems an unstored calc is not as unstored as one would think, and including a trigger can help. Now I will have to re-examine other files with refresh issues, to see which ones can be fixed in the same way.

JMO and Dick Honing discusses this topic intensively in the Aug/Sept issue of Advisor, and the issue has always been there, just moved to new locations when getting to FM7, take a look at this oldie:

http://www.dwdataconcepts.com/dl/tm/UPDATE_CALCS.sit

...but where it bothered these two gentlemen was filtered and sorted portals, where variations over the two methods we here demonstrates is investigated - Dick's problem is due to his extensive use of Singlefile Interfaces.

--sd