Help with an Excel like formula in Ninox

I cannot believe the level of support that I can get from just posting here. Cannot thank you enough. This is seriously going to help me so much. No doubt I will do what you have said and run into issue but it gives me a solid starting point. Thank you!

Hey John, 

To create the Join table that has Teachers down the side, Periods across the top and then a field for subjects.....how do I reference this table to the periods so that I can have all the options for periods across the top? 

I have a:

- Teacher Data table

- Periods Table

- Join table (that I cannot figure out how to get the periods M1 m2 m3 etc across the top) - I think this is what I originally had as my Master Grid but hadn't set up right.

- Subject table

 

I have attached a few screen shots that might help to show where my misunderstanding is and the data model too.

Im clearly doing many things wrong!

Appreciate it so much. Whenever you get the time. I totally understand not being able to do it until you have time to. 

 

Hmm.. it might not be able to be used for the purpose that I am after then. The whole purpose of the excel spreadsheet is to generate a teacher timetable for when the students have to go off to a specialist class such as Physical Education (PE1 or PE2) for example. The only way this time table can actually get created is if I do a heap of calculations prior about availability of time, teachers required amount of time etc. Then finally the final stage is plotting it on the timetable. So the purpose behind the database approach is to collate all the data prior to putting it onto this Master Grid. In Excel I just use drop down boxes and choose the subject. 

You can see in Picture 1 - this is where I allocate all the teachers to a specialist teacher and also then have formulas that read the Master Grid (picture 2) to tell me if it equals what I said they needed. I do this through a countif formula. Count if....."pe1"

 

(Picture 1 - Availability of Time)

 

(Picture 2 - master grid)

 

So the three in yellow:

Available Time

Allocating Specialist Time

Current Allocations. 

 

The teacher data table is where I input all teacher records (master data). I say how many specialist classes they each require (a specialist class gives them an hour free from students to do prep and its called DOTT - duties other than teacher). So in this table I calculate the required DOTT and how I am going to be able to give it to them in terms of periods (i.e., 5 x 55 min periods = 275 minutes of DOTT and they are mandated to received at least 270 so that means if I give them 5 specialist classes they receive that they require). By doing this for all teachers, it then tells me the total amount of 55 min periods I need to allocate to each teacher and I sum it up. See screenshot of table.

 

(teacher data table)

 

(Teacher data fields)

 

The next step in the process is caculating how many periods of DOTT each specialist can actually provide to compare to the amount of DOTT all teachers required (from teacher data)

 

(available time - table)

 

(available time - fields)

 

Then the next step of the process is to decide how I want to actually allocate those periods of DOTT to the teachers. I.e., which specialists will take which teachers class. Not when they will take it. But just how I want to spilt their time across the school. For example, Indonesian is only upper so I wont be allocating a lower teacher a Indonesian DOTT. Science needs to always go to upper before kindy. So if the science teacher only has 22 periods of DOTT to give in the week lets say, and theres 25 teachers.....three lower teachers need to miss out. This table also has the column of "Difference" which compares the total number of DOTT periods I have allocated to each teacher, and compares against the master data to make sure they arent over or under.  

 

(Allocate - table)

 

(allocate time - fields)

 

Then the last table is the current allocations which is set up entirely incorrectly. But this will have the same fields as the Allocate time table but instead I want to do the "Count if" formula to read the master grid to count how many periods of each subject the teacher has and then compare it against the Allocate Time table to make sure based on how I allocated the time, it is right. So if in the timetable I give someone 2 PE1 sessions but in the allocate time table I said to only give them 1 - it flags to say they are over. 

 

---- I haven't written code before but I did just enrol in a software development course! But that doesn't start for a long while. I am a super quick learning though.