Filtering events in a Calendar Control

[ecalan]

I am using a calendar control (not component) to display events from a database as described in the alpha documentation quoted and linked below:

Looks great with my app but I can't figure out the last step. It is a multi-tenant app and I need to filter the events by the logged in user.

The section of the xbasic code that creates the Json data for the calendar is below. I have tried adding the following below the select statement:

WHERE username= session.username
WHERE username = Context.Security.CurrentUser
WHERE username = :username (tried several ways to pass in this argument).

Hard coded as WHERE username ="paul" does work.

Any ideas on how to filter the SQL data in this context?

From the Alpha documentation:

dim jsonData as c
dim def as p
dim defn as p
defn.type = "sql"
defn.connectionString = "::Name::AADemo-Northwind"
defn.tableName = <<%sql%
SELECT Orders.OrderID as OrderId,
concatenate(orders.orderid,': ', customers.companyname) as eventName,
Orders.OrderDate as orderDate,
concatenate('Order: ', orders.orderid, ': ', customers.contactname, ' ', customers.companyname) AS eventdescription
FROM Orders Orders
INNER JOIN Customers Customers
ON Orders.CustomerID = Customers.CustomerID
%sql%

defn.startDate = year(startDate) + "-" + month(startDate) + "-" + day(startDate)
defn.endDate = year(endDate) + "-" + month(endDate) + "-" + day(endDate)
defn.eventDate = "orderDate"
defn.eventId = "orderId"
defn.eventName = "eventName"
defn.eventDescription = "eventdescription"

jsonData = a5_GetCalendarEventDataJSON(defn)


From:
https://www.alphasoftware.com/docume...%20Definitions

[ecalan]

Re: Filtering events in a Calendar Control

Solved. This is one of those where you spend 4 hours one day with no luck, sleep on it, think of a new search term, and find the answer in 5 minutes the next day.

Simple bit of code:

dim arg1 as C
arg1=e.session.username
defn.filter = "username = :user"
dim args as sql::Arguments

args.set("user",arg1)

defn.argumentsXML = args.XML

[mattcarroll]

Re: Filtering events in a Calendar Control

Eric - THANK YOU! For anyone else, to be clear on the placement of Eric's great work (which matters apparently LOL). For context, TCID is the unique ID of each training center subscribing to my course management system.

defn.type = "sql"
defn.connectionString = "::Name::training_centers"
defn.tableName = "events"
dim arg1 as C
arg1=e.session.tcid
defn.filter = "tcid = :tcid"
dim args as sql::Arguments
args.set("tcid",arg1)
defn.argumentsXML = args.XML
defn.startDate = year(startDate) + "-" + month(startDate) + "-" + day(startDate)
defn.endDate = year(endDate) + "-" + month(endDate) + "-" + day(endDate)
defn.eventDate = "starttime"
defn.eventId = "eventid"
defn.eventName = "eventname"
defn.eventDescription = "eventdesc"

[ecalan]

Re: Filtering events in a Calendar Control

Matt, glad that two year-old post was useful for you.:) For anyone else who comes across this, I have been using the calendar control as a calendaring solution since then and I highly recommend it for any repetitive high-volume calendaring of similar events. As a public defender type lawyer with 3-10 cases a day that need to be re-calendared for the next, usually while "on the record," it is perfect. The advantage in this environment is that a separate calendar table is not needed as the displayed info is drawn from existing tables with just an "event" date added to a standard notes table. In my usage, a user only adds a two-letter code. The calendar displays a concated field that includes client name (8 chars), city(1 char) and the two letter code. It also makes the calendar very simple to read - to easily find good dates when a judge bellows "give me a date the week of the 9th" - with all entries identically formatted and up to date. The hardest part is deciding what to include in the concat field so it always fits on on one line in the calendar.

The exceptional text tutorial from Alpha linked above was easy for a hobby programmer like me (except for that last bit to make it multi-tenant).

I have included an image of my calendar this month with some slight redaction so you can see how homogeneous it turns out. I did add an "add event" button which opens a new note/event (one record is both) linked to the selected date in the control and to the client from a daily notes/events page part of which can be seen under the calendar window in the image.

Of all the things I have built in 15 years of programming with Alpha (I do go months without opening it at all), this calendaring system is, I think, the coolest thing I have come up with. While it is only right for some usage environments, I would be happy to share more about the solution if anyone is interested.

calendar.png