Self taught Filemaker user here, wondering if I can gleam some ideas concerning a new solution I've been tasked with creating. The company I work for have requested a workplace utilization app, for tracking office wide seating arrangements on several floors (i.e. is the seat active, away, inactive etc...). Timestamps for every seat analysis are required, but very little else. There are 1500+ chairs across 4 floors on this particular job.
I have created a solution that will adequately work for one such survey; The source table for the solution includes:
Source table fields:
Chair Number: Text
Floor Number: Number
Column Number: Number
Int_Ext position: Text
Chair Status 1: Text
Timestamp 1 (scripted to function when each individual seat status is altered)
Chair Status 2: Text
Timestamp 2 (scripted to function when each individual seat status is altered)
Chair Status 3: Text
Timestamp 3 (scripted to function when each individual seat status is altered)
Chair Status 4: Text
Timestamp 4 (scripted to function when each individual seat status is altered)
Problem I'm facing:
The client has requested that multiple surveys be taken four times a day (x4), four times a week (x4). As shown in the pictures I have used a portal to allow four sets of surveys (Shown below) to take place a day, but this still leaves the issue of being able to recreate this information in another table for the 3 remaining sets of surveys.
My solution is currently geared up to store the information in the source table, and works well when doing this, however when it comes to carrying out another survey I do not want to delete the values from the first survey table, rather archive that table of data for comparison/analysis at a later date.
I started off with idea of designing a horizontal database that included numerous fields for all of the chairs (so I could effectively turn each row into an individual overall survey), however this would have resulted in 3500+ columns (even more than that probably) which would make the database sluggish, not to mention hard to adapt for future projects.
Whilst I now have a relational database in place, I still cannot see how to generate further surveys (i.e. further tables of data) within my solution.
Apologies if this is not enough information to begin with but bear with me, this is the first community post I have made, so I'm still learning the ropes. Please request something if it would help you understand the above in more detail. I realize the question title is poor as well.
Any help offered, or suggestions of resources to reference, would be greatly appreciated.