You say it does not work: what do you get back? A "?" or an empty result?
You may want to try adding parenthesis to make your request a little more explicit
luEventDate BETWEEN xg_EventDate and xg_EventDateEnd AND
luEventDateEnd BETWEEN xg_EventDate and xg_EventDateEnd
I got back a ? with and without parens :/
My layout is based on Talent The portal is another TO of Talent called Talent_portal. The portal needs to show the whole talent pool. And each row should display different color based on availability status. I had it in a list view before and had 5 different relationships checking date availability for overlaping dates, within, etc but this caused the layout to load slowly because the global was being set. That is why I decided I use SQL since its a simple BETWEEN date AND date the only trouble is checking if the portal row ID is in that list that SQL returns.
The ? usually indicates that there is a syntax problem with the query.
If you don't have it already, download Query Builder and read the instructions, it has the sql debug custom function built in that will tell you exactly what the error is:
If that does not solve it, post back with the error returned and we'll figure it out from there
awesome Ill download now thanks wim!
Note that just before the double quotes at the end of the line you have a single quote. I think that is causing a syntax error and is why you are getting a ?.
hey wim so it seems to not be reading the ID of the portal row I think thats what the issue is. I have a layout where i do query testing and if I put int the ID manually 'DJ0001' it returns a value. So perhaps its because its in a portal it doesnt know how to read it?
Date comparison must be SQL date format, IIRC.
and/or perhaps your globals are not date types and you must
not enough info.
A simple calculation using relationships is probably better than ExecuteSQL() in this case.
ExecuteSQL() will evaluate all records, not just the ones filtered by relationship. And since you're using BETWEEN, it won't use the index. It's going to be pretty slow.
A properly constructed "non ExecuteSQL()" calc makes more sense here. For portal filtering, and especially for conditional formatting.
I actually originally had relationships and calcs but when the global would be set it would take forever to load the list of data. For that reason I thought if I have a execSQL calc check the availiability of each portal row it would be quicker. O this optimization idea doesnt seem like it was optimal lol
Sorry, I missed that post in this thread.
What is the goal here? Not the method you're choosing to accomplish that goal, but the user experience you desire? Why are you viewing a list of DJs from the DJs table?
Based on the description above, I would assume you are trying to book DJs for events. You could use the Events table and show a list of available DJs.
And one thing I realized a while back when dealing with overlapping dates is all you need to check for is:
( Start Date < End Date 2 ) AND ( End Date > Start Date 2 )
If this statement is true, there's an overlap. If it's false, there's not.
View ALL talent whether available or not and display thru conditional formatting their type of unavailability (vacation, booked, etc)
Reason : The Talent might be marked as unavailable that day but it might just be to a certain time so user wants to view them even if marked unavillable.
The filtered relationship in checking for availability is the bottleneck since there are 5 rellationships. We have start dates and end dates because a Talent can be spinning for a week on a cruise.
You've got a couple things going on here. Displaying certain records, and indicating something about them.
Why are you viewing this through a Talent layout? Why not through Events? You want to see who's available for an Event, right? Your relationship can be based on the Dates of the Event rather than global dates.
What are the five relationships you need? It feels like most can be based on the Event table and not globals.
It seems you don't need to see all the talent, just the talent that doesn't have an overlap, or has an overlap of just the start and/or end date.
A good process is: Reduce the number of records you show in the portal using relationships. Then apply portal filtering to that reduced set. Then apply conditional formatting to those records.
Something to keep in mind is that a portal filtering calculation acts on each available child record. So you're running that ExecuteSQL() on every single record (loading the entire record) in the Talent table.
Relationships use indexes to filter available records. Using indexes is much faster than loading the entire contents of a record. You want to only use portal filtering on a small subset of records.