Prices changing in time and data import
Hi,
I'm almost done with the basic structure of a database for managing school payments and I have these problems:
1. averagely, students stay in school 3 to 8 years, and every year almost all the prices of the different activities change. I want to be able to invoice payments either for the single year or for all the years, and in this second case it means that the same activity needs to be invoiced with different prices. Is it possibile? Or should I create a new "catalogue" every year?
2. How would I list in an invoice all "subscriptions to activities" and/or all the payments done by a student?
3. I need to import all the data that are already stored in a sheet: since there are a lot of multiple choice field and calculation fields, which is the best way to speed up the process? I thought to create additional columns in the sheet, preparing it to contain the corresponding Ninox ID's. Is this the best way ?
Thanks to all who will help :D
5 replies
-
My thought on #1 is not to duplicate your catalogue for each year but create a child table that tracks price changes per activity. It could be as simple as 1 date field and 1 number field (well of course you need the reference field to Jobs as well). I think DB best practices is to not have duplicate tables that track the same data. You then have to write code to different tables depending on year and that seems like too much work.
I was thinking that you can just do a number field to track years, but I'm guessing your school year crosses calendar years so you need to know which school year the price changed. If you already have a table that tracks school years, then you can create a reference field or a dynamic choice field to that table so you can associate prices changes to school year.
-
I thought I would try to mock up a possible solution. Here is how I thought of solving #1 and #2.
There is a table called activities with a child table called bySchoolYear and a child of that called studentRegistered. I originally was thinking of making a child table under Students where you add the activity they sign up for, but then realized that it is more logical to be in the activity of the school year then add the students to the activity. Now you have the ability to set the value of the activity by school year and you get a roster of students for that year as well as well as other years. Plus it would seem like you would get a list of students that have signed up for an activity and then you have to enter the data by activity.
Then if you go to Invoice, you can create a new invoice. Select a student and you should see under Activities to Bill one or two activities that haven't been billed yet to the student. You can select one or multiple activities and then the Add Activities button will appear. When you click on the button it will add the selected activities that haven't been billed to the current invoice.
You will see a view table to the activites that the student selected above has signed up for as a way of double checking.
I hope this helps.
-
About #3, so your sheet has multiple choice fields and calculation fields.?
Are your fields in Ninox that relate to the calculation fields in your sheet formula fields? If they are then you only need to import the data that the formula fields use. If they are data fields (text, number, date) then you need to make sure the sheet has just the calculated data.
Content aside
- 1 yr agoLast active
- 5Replies
- 62Views
-
2
Following