Hello,
I'm working on Seating data from our existing SQL Server database that I want to implement in Dataverse. The basic structure has a record for a Seat and the seat is related by Lookup to a table of Rooms and is given an integer identifier to indicate which seat in the room is indicated. Each seat can also have a contact assigned. The result is a Seating table that looks like this, more or less:
Seating
---------
Seat (primary column name, contains value like "RoomName - SeatNumber"
Room (lookup to Room table - required)
SeatNumber (ID for seat which starting left from the room entrance is numbered clockwise 0 to N, where N is the total number of seats in the room - a room may have no seats if it's a lab for example)
Contact (lookup to Contact table - optional)
Overall this is pretty much where I want it. I had to adapt the way I handled seats and seat ids on the SQL Server side to create a functionally similar feature in Dataverse but I think it'll work. What I'd like to accomplish to make the lives of my users easier is to figure a way to automatically populate the Seat ID. Starting out with the initial data import from SQL Server I'll have all the information I need, but moving forward I'd like the functionality of adding or removing seats to have features that will appropriately adjust the data as needed. Specifically, I'd like the SeatNumber to be calculated as the MAX(SeatNumber) for the current Room plus 1. Pretty simple idea. I'm not sure how that could be implemented in Dataverse though. It's easy enough to articulate - get me the max value of the seat number for the group of records for the current room.
If I could get any insight or advice on how to approach this I'd appreciate it.
Thanks,
Dave Spaar
