[Solved] Resource view not working with unorthodox database schema

[Daniel]

Hello,

I am trying to set up the Scheduler so as to get a view of techs and their availability. The catch is, the database does not conform to the design that the Scheduler appears to want. The <telerik:ResourceType> tag needs a data source, a primary key (for the tech), and a foreign key (for the appointment). The problem in my case is that the primary key of the appointment is not in the same table as that of the tech. There is an Appointments table and a Users table, and the two are linked via a third table containing the PK of the user and the PK of the appointment.

I have tried to work around this by using a SqlDataSource with the following SQL query:

SELECT A.ID AS apptID, U.userID, A.name, A.[dateTime], A.dateTimeEnd, U.userName<br>
FROM AppointmentUsers AXU<br>
JOIN Users U ON AXU.userID = U.userID<br>
JOIN Appointments A ON AXU.apptID = A.ID

Using this, I'm able to get a list of techs to assign the appointment to when I edit the appointment. However, I'm still unable to view the resources on a given day. Below is a full code sample:

        <telerik:RadScheduler ID="Scheduler"
                              DataKeyField="apptID"
                              DataSubjectField="name"
                              DataStartField="dateTime"
                              DataEndField="dateTimeEnd"
                              DataSourceID="AppointmentsData"
                              runat="server">

            <DayView ShowResourceHeaders="true" />
            <WeekView ShowResourceHeaders="true" />
            <MonthView ShowResourceHeaders="true" />
            <TimelineView ShowResourceHeaders="true" />

            <ResourceTypes>
                <telerik:ResourceType Name="Tech" KeyField="userID" ForeignKeyField="apptID" TextField="userName" DataSourceID="AppointmentsData" />
            </ResourceTypes>




        </telerik:RadScheduler>

        <asp:SqlDataSource ID="AppointmentsData"
                           ProviderName="System.Data.SqlClient"
                           ConnectionString=xxx
                           SelectCommand="SELECT A.ID AS apptID, U.userID, A.name, A.[dateTime], A.dateTimeEnd, U.userName
                                          FROM AppointmentUsers AXU
                                          JOIN Users U ON AXU.userID = U.userID
                                          JOIN Appointments A ON AAXU.apptID = A.ID
                                          WHERE A.companyID = 7673"
                           runat="server" />

Thanks,

Daniel

[T. Tsonev]

Hi Daniel,

I can confirm that declarative data sources are not suitable when you have many-to-many relationship between appointments and resources. You'll have to create a custom provider and we have a tutorial to help you out:

http://www.telerik.com/help/aspnet-ajax/schedule_databindingimplementingaproviderthatsupportsmultivaluedresources.html

The sample code from the tutorial is available in the Live Demos App_Code/Scheduler/MyDbSchedulerProvider.cs file.

This will give you full control over how data is retrieved. You can use LINQ2SQL or other ORM tools if you like to.

I hope this helps.

Best wishes,
Tsvetomir Tsonev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.