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.

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.

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

See attached.

bookingsVsUnassigned.fp7.zip

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

That's really helpful, many thanks again.

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?

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.

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

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.

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