Challenging Relationship

["... you mean these fans?"]

Hi,

 

I have a relationship that is troublesome.

 

Basically, the table field I need is not able to index via a calculation. Hmm :-(

 

In the client table, a value is entered depending on the client status. ( TEXT )

 

Over in my remedies table, I need to see this field.  Which I can build a relationship via my remedies table to my client table.

 

However, this field can not be index and used to build another relationship on my remedies table.

 

Basically, I was hoping to use the data in the client table to determine a "=" in a relationship on my remedies table.

 

I can hard code the client status when creating the remedy record into a field on the remedies table, but if and when the client status changes, the relationship would still be valid ( since it is hard coded ) and show client data even though the client "STATUS" has changed and no longer valid for this relationship.

 

Any assistance in this matter i would be grateful.

 

Thank you.

 

Tom :-)

[email protected]

 

 

[comment]

I am somewhat confused by your description: I think the relationship you have between Clients and Remedies is 'one client has many remedies' - is that correct? Now, what is the other relationship you are trying to establish and for what purpose?

["... you mean these fans?"]

Hi,

 

I've built a calendar and on the calendar, we look to the remedies.  And my client only wants to see remedies based on the client status fields and date. Hmm

 

If the client status changes I need to have a way so the client status on the remedies record reflex this change.  Currently I can only hard code this entry into the remedies table for the relationship to work.  If the Client Status changes, I have to do a loop.  would like to try and avoid this.

 

I can build a calculation field on the remedies table that looks to the client status but when I use the remedies clients status to build the calendar relationship the client status currently does not index.  So the relationship from the calendar to the remedies does not build.  On my calendar/remedies relationship map I have

 

Remedy::Date = Calendar::Search Date ( Only this for the moment )

Remedy::ClientStatus = Calendar::ClientStatus ( Removed this, but I need this also to work )

 

The remedy client status is a calculation that looks to the client table.  But it won't allow me to use it in my Calendar relationship to build the remedy match fields.

 

I hope this helps.   Any assistance will be grateful.

 

Thank you.

 

Tom

[email protected]

[comment]

I see. The simplest method would be to base the relationship on matching date only, and show the related records in a filtered portal. Unlike relationships, portals can be filtered by unindexable data. Alternatively, you can use the so-called "Ugo method" - search for it here on the forums.

 

I wouldn't expect any of the two to be very fast, so if you have a large amount of records to show, you may have to resort to denormalizing your solution - i.e. run a script every time you change the client's status in order to propagate the change to the related remedies. But I wouldn't recommend this unless it's really unavoidable.

["... you mean these fans?"]

Hi,

 

Thank you. 

 

I will try it.  I have the calendar already filter so let me look at that a see how to modify it.

 

Tom :-)