0

Help with an Excel like formula in Ninox

Very new user here! I am transferring my Excel spreadsheet into Ninox and have hit a road block. I am creating a whole school timetable.

I have multiple tables including: 'P55_Current Allocations', 'Master Grid' and 'Teacher Data'.

I have 32 columns on Master Grid. The first two columns are pulled from fields that are in Teacher Data (Code & First Name). The rest are in the form of  M1, M2, M3 M4 M5 M6 (6 school periods a day, 5 days a week M1 = Monday Period 1). Each column has the option to select the class a teacher has on that particular day in that particular period. For example: PE1, MUS, ART.

I want to read all data in a row (i.e., for a particular person that I have populated from 'Teacher Data' on the Master Grid). I want to count for that person how many times the word "PE1" appears. On the 'P55_Current Allocations" table I want to populate the amount of times that the word "PE1" appears. 

I have a formula in excel that does this for me. =COUNTIF('Master Grid'!$E2:$I2,"PE1").

I literally have no clue what to do. I have tried a few different options but seem to fall short every time. I think the trouble I am also having is because I need to make sure it links the teachers together? so it doesn't read a different teachers row? 

any help is beyond appreciated!

9 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi

     

    Moving from Excel to a database can require a bit of thought. Each sheet can look similar but require different treatment in a database.

     

    Example 1. A single table

     

    This import would require just a single TEACHER table to be created and the excel sheet imported into it.

     

    Example 2. A one to Many relationship

     

    This is best re-created with 2 tables. A TEACHER Table and a PHONE table in a one to many realtionship, so that a teacher can have more than one phone number, but a phone number belongs to just one teacher.

     

    Example 3. A Many to Many relationship

     

    This many to many relationship requires three tables, a TEACHER table (in blue), a PERIODS table (in green) and a JOIN table (in yellow) to join the two together. This join table has a relationship to TEACHER and a relationship to PERIODS and a field to hold the subject.

     

    I would create a fourth table SUBJECTS and add a relationship in the join table to this too, rather than using a text field. Once you have this in place thne you can create another many to many join between teachers and subjects (a fifth table) to hold the counts you are looking for.

     

    Regards

     

    John

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Sorry... Re Example 3.  PERIODS in yellow and JOIN in green

    • emspurser
    • 2 yrs ago
    • Reported - view

    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!

    • emspurser
    • 2 yrs ago
    • Reported - view

    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.

    • emspurser
    • 2 yrs ago
    • Reported - view

    Im clearly doing many things wrong!

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Glad we can help.

     

    I'm busy for a few days but will try to reply in more detail over the weekend, if not before.

     

    Unfortunately you won't be able to re-create the look of the spreadsheet (the nearest available view is a Pivot which may or may not be good enogh for you), I was aiming at getting an understanding of the structure of the data. We will create a small piece of code to creat a Join record for each Teacher record and each Period record ready to be filled in with a Subject. Have you written any pieces of code before?

     

    In the meantime, can you explain the jobs of the three tables highlighted in yellow. And can you show me the field names of your Teacher, Period, Join and Subject tables (got to Edit fields... and take a shreenshot of each).

     

    Regards John

    • emspurser
    • 2 yrs ago
    • Reported - view

    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. 

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi

     

    Can I ask, are Teachers and Specialists different people or can a Specialist be a Teacher and visa versa?

     

    Thanks John

Content aside

  • 2 yrs agoLast active
  • 9Replies
  • 738Views