can i use a 'query' in a calculated field?
i have a database of clients, with 2 tables, a client table and a gig table (I am an entertainer). I can 0-many gigs for a client.
I would like to have an indicator on a client form, showing whether I have a gig scheduled in the future for this client (so that I don't call that client )
My first idea was to create a calculated field, which would be set if there was a row in the Gig table for that client, that had a show_date in the future. My problem is that I'm not sure if that is possible, or how to do it.
The sql query would be something like
select 'y' from Gigs, Clients where Gig.client_id = Clients.client_Id and gigs.showdate > today.
Any thoughts would be appreciatted.
Never mind...I figured it out.
I added a summary field in the Gigs table, with a 'maximum' show date. I then added a calculated field in the Gigs table which determined if the maximum show date was greater than today.
I then was able to add the calculated field as a portal to my client layout..and it appears to work.