- I have a table called SEMINAR which contains seminar data.
- Each seminar lasts for exactly 1 week.
- Each week we have multiple seminars starting on the first day of the week, and ending on the last day of the week.
- I have a separate table called WEEKS which contains the start and end dates of each week.
- The SEMINAR table foreign keys / Connects to the WEEK table in order to specify the week that each seminar runs over.
I want to add a GridView to my Classic app so that it only displays the current seminars i.e. display SEMINAR row data for rows whose correspondingWEEK row has a [StartDate, EndDate] time interval that contains the current date.
How can I achieve this? The suggestions from the AI (restrictions) do not work, and I can not access the fields of the WEEKS table in order to apply filters against them.
On the assumption that the start and end dates of WEEKS donāt change after they are set (i.e. they are effectively static reference dates) then you could consider using record rules to copy those dates across to duplicate fields in the SEMINAR table every time you create / edit a seminar. That way the start and end dates, as well as the WEEK, will be available as local fields in the SEMINAR table and can be used as filters for your grid view.
Thatās an ugly solution but yes, youāre absolutely right! Iām hoping that Knack is willing to support fundamental relational database functionality. If i had SQL access I could do this in my sleep, but that doesnāt appear to be an option 
Yes, Knack works but is not always elegantā¦
I may be misunderstanding but the grid is based on the SEMINARS file where you are possibly better approaching from the other direction.
Do a grid of the weeks file, use text formuals to access the seminar information.
You would therefore have much more control over the date and items being displayed ?
I have a somewhat similar situation where one SOLDIER FILE (SEMINAR) can connect with multiple WIA events (WEEKS)
So by basing the grid on the WIA events (WEEKS) file, I can use filters to query based on the current week parameter.
And with approriate TEXT formula you can get information relevant from the SEMINARS file.
i
If I have misunderstood your issue please excuse my interruption.
Interesting twist Ray, but I donāt think that would work quite the way he is looking for. Iām assuming there is only 1 entry for each week in the WEEKS table, so if her creates a grid of WEEKS there will only ever be one row shown if it is filtered by āSTART is today or before and END is today or AFTERā. If you included the connection for SEMINARS it would list them all, but all crammed into the one cell, like below, which is not ideal:
1 Like
I was thinking more along the lines of one to many. Seminars to Weeks. In that way Seminars are available to be used multiple times for multiple WEEKS entries the common denominator being weeks and seminar.
To have multiple seminar entries in a single WEEKS item may reduce the number of weeks entries to 52 in a year but is far less practical when it comes to accessability and reporting.
By creating an entry screen where an item is created for each week and each seminar from the connected file you have a lot more items yes but a far more accessible and workable grid result.
WEEKS items
Weeks 1-7date. Seminar - A
Weeks 1-7date. Seminar - B
Weeks 8-14date Seminar - B
So a grid display would give each seminar as a separate line and opens the whole spectrum of sorting and analysis using filters on the data
Weeks Reporting -
sorted by weeks or filtered for a specific week gives u a workable grid
Weeks 1-7date | Seminar A | other seminar data
Weeks 1-7date | Seminar B | other seminar data
Weeks 8-14date | Seminar B | other seminar data
I already tried that, it didnāt work. I need to filter using columns from both tables.
I presented the seminar example as a cut down version of the problem. There are different types of seminars, and each type needs to appear in a different view.
Can u add a category to the seminar which then becomes another filter for the grid result ?
Get BlueMail for Android
Already there. Fundamentally, the problem is:
The metadata for a Seminar - such as the Category - are mostly inherited from āSeminar Seriesā; and the Duration comes from the āWeeksā table.
Seminar Series : Seminar is 1 : n
Seminar : Week is m : 1
I need to filter Seminar entries based on columns from all 3 tables.
Iāve had to de-normalise the data in order to accommodate Knackās limitations.
Thereās a different View for each category because they relate to user access - each category is only visible to 1 single user role.
Like I said, if I had full control over the SQL for source data extraction this would be easy, but when the relationships become realistic I hit the limitations of Knack.
