I’ve recently begun exploring the use of Access Services with O365 just to see what this new capability offers to businesses and developers. Overall, I’m really impressed with some of the complex things that can be done with Access Services.
Setting the stage, let’s say I am working for a company that will be organizing a conference. This conference will be made up of several tracks with multiple sessions. Each room should only support one session per time slot. The conference organizer should not be allowed to double book a room. That would be bad. Based on the listed scenario, the logic to meet this requirement is:
- Find out how many sessions are assigned to the room and slot.
- If there is more than 1 session assigned to a room and a slot, don’t let a record get created.
- Notify the person inserting or updating the record that the room is already occupied.
In the database, the following tables have already been created:
Rooms is simply a list of available rooms for the conference sessions. There is not much to show with this table.
Sessions are the individual conference break-out sessions. In this table there is a references to rooms and a reference to slots.
The final table, slots is just a simple list of the various time slots.
There is not an easy way to get a count of records with the built in data macros, but this is access and queries are great at this. A query is created that to find out exactly how many sessions are registered for a specific slot and room combination. Adding a query is done by clicking on the Advanced button and selecting Query. (I would also recommend turning on the Navigation Pane so you can see the structure of the assets that make up the database.)
The only table that is needed for this query is the Sessions table since it holds the references to both the room and slot tables. Since the query needs to know what room and slot combination to check, two parameters that will be created. This will help later by providing an easy way to request the data from the query. Click on Parameters in the ribbon.
Two parameters need to be created, one for the ID of the room and another for the ID of the slot.
Also, this query is going to be used for calculations. Turn on the totals allows the selection of count and where that will be a part of the query.
The first column will be a simple count of the ID. The next two columns will be Where fields set to the room and slot and utilizing the parameters created previously.
I save the query and give it a really nice developer name: qryCountSessionInRoomAndSlot. Now it’s time to create the data macro that will be used to help enforce a single session per slot and room. Again, this is found on the advanced tab in the ribbon.
This data macro will need to take the ID of a slot and the ID of a room and execute the query that was created. This means two parameters will also need to be created in the data macro. This is done by clicking on Create Parameter and provided the details of the parameter. Here, I have added both parameters needed.
Now to execute some logic. This macro is going to execute the query, so the Look Up A Record action should be used. When the query is selected, it will automatically show the parameters needed for the query. These need to be mapped to the parameters that are going to be passed into the macro.
Finally, the macro needs to output the resulting data. This is done by using the SetReturnVar action. I set the name of the variable to be CountOfFilledSlots and the expression maps to the name of the column that is to be returned (in the query, this is CountOfID).
Save the data macro and give it a name. I called it GetRoomSlotCount. The data macro completes our requirement to find out how many records already exist for a specific slot and room. If we run this macro, it should return 0 or 1, which are good values since a room can either have a single assignment or doesn’t have an assignment at all. If it returns the value 2, we have a conflict that needs to be resolved. Now we need to enforce that only a single session can be assigned to a room and a slot utilizing this new data macro. Open the Sessions table in design view.
In the ribbon, there are three events that we can use to write macros against. Begin by clicking on On Insert – this will execute when new records are inserted into the table.
The first step in our logic is to execute the data macro that was created. The action needed is RunDataMacro and the macro to execute can be selected from a drop down. Once the macro is selected, it will provide the input and output parameters. They simply need to be mapped to the appropriate fields.
Notice that the output variable CountOfFilledSlots automatically requests to set a local variable. Just provide it a name that is meaningful so it can be used in the next step. The next step will be an If action. This evaluates a simple logical expression for a true or false evaluation. This macro will execute immediately after the record is updated or inserted into the table. This means, if we have a conflict the FilledSlots varialble will have a value greater than one.
Now, we need to take an action if the condition is satisfied. We want to raise an error which will cause a validation failure and will rollback the requested update or insert.
This completes the logic for the insert. We can just copy the same logic to the On Update event. In the application running in SharePoint, it’s time to create a record that will cause a conflict:
When the application detects a conflict when running in SharePoint 2013, the user receives an error message and has a chance to change the data to try again.
Clicking OK allows the record to be corrected.