Need help organizing some information
Hello Ninox forums! I've been using the macOS version of Ninox for about 2 weeks, its the first time I've ever sat down and got serious about using a database to keep my business organized.
Currently I've got a few tables:
Contacts
Companies
Productions
For any given Production, I will hire some assistants to help me. Up until I started using Ninox I would use a spreadsheet to help me visualize 8 days around the dates of a job, and put each assistant in a cell under the dates, color coded with the kind of work they are doing.
I cannot figure out how to express this data in the same way inside Ninox. Any ideas?
Thank you!
30 replies
-
Welcome to Ninox, . Welcome to the world of relational database. It is a very different world than the flat spreadsheet world, so be open to unlearning what you are use to.
Now on to your question.
said:
For any given Production, I will hire some assistants to help me.You will probably have more than 1 assistant per production record. You might be tempted to create multiple assistant fields, i.e. a text field called assistant1 and another called assistant2, etc. If you ever find yourself wanting to create multiple fields that hold the same kind of data, what you need is a sub/child table.
That means you will need a table to store your assistants/workers/employee data we can call it Workers for now.
Then you will create a new table called Assistants and then put a reference field to Production and one to Workers. Then change the Composition parameter of Production to Yes. This makes Assistants a child of Production.
Why make it a child of Production and not workers? Well a record in Assistants wouldn't exist without a record Production. A record in Assistant can exist even if there is no record in Worker, it could be a place holder.
Now when you create a record in Production you will see a table called Assistants. You then would click on the Create Record button at the bottom of the table and it will then create a new record in Assistants and automatically link it to the record in Production (in fact this link is hidden when accessed through Production) then allow you to pick the Worker you want.
Give that a try and let us know how it goes.
-
That looks great. You have done a great job.
said:
I have a relationship table setup in each job that shows which assistants I have employed oone additional field, if they were my lead or not. This is where i've stagnated and have stumped myself.What is your question?
You may want to consider making a People table and then use a multichoice field called Position where you can track if a person is a photographer, assistant, cinematographer, etc. I'm sure people can do multiple jobs, so this way you don't have to move people between tables. In fact they can also become clients or contacts for production companies.
So you want to try to not copy data when you don't have to. Keep all data that is similar together.
-
Thanks Fred!
I guess my question is related to this excel sheet I used to use to make a labor schedule/breakdown.If i have a relationship table setup with my assistants, and in that have a multiple choice field where I can say "over these 8 days, McCoy K is going to work Trucking, Build and On-Set" how can i make display that information in my production table arranged like this.
Is there a way to take information from a relationship table and display it as text fields with drop down menus next to them, dynamically, as more information is added to that relationship table?
-
Absolutely - I've uploaded a test database that is a copy of the one I'm currently using but with test information entered. Take a look and let me know what you think!
-
Also - i've filled it out as if I were entering in all of my real information!
-
Here is a first go at the code:
Attached is the modified DB. I created a new table called Labor as I needed dates and people linked.
I couldn't figure out a way to programmatically create columns in a table, so I ended up creating a table for each date. Then I nested each of them inside another table so I can show the dates horizontally. Kind of old fashion, but quick, well kind of, and dirty.
I hope your html/css skills are decent as now you can do more formatting.
I will probably try using grids to see if that will be cleaner.
-
said:
"Day 1" "Day 2" etc (ultimately I would like there to be 8 days total, as, historically this has been all i would need to schedule.Just a reminder that duplicating field names that store the same type of information can cause problems in the future when performing summaries and such. If you find yourself wanting to do this you should consider creating a sub table. Take a look at the Labor table I created in the DB I uploaded.
The sub table allows you to have as few or many days. You said you will only need 8 days, but what happens if you have a 9 day job (cause your business is growing)? Then you have a 12 day job.
said:
Is there a way in Ninox to use a formula to change the display name of a column in a table?When you click on the column, the edit window opens and you can type any name you want. Sadly the name can not be based on a formula. If you want this then you will have to create HTML tables like in my example.
-
Messing around a bit more I got this:
with this code:
let getLaborData := (Labor order by Date); let getUniqueDates := unique(getLaborData.Date); let data := for loop1 in getUniqueDates do { date: format(loop1, "dddd DD"), worker: (getLaborData[Date = loop1] order by Type).{ name: Contacts.Name, type: Type } } end; let css := " <style> .outsidetable { width: 100%; } .outsideTR { vertical-align: top } .insideTable { border: 1px solid black; width: 100% }; .insideTR { border: 1px solid black; } .insideTD { font-weight: bold; } th { height: 45px; font-size: 110% } .headBck:{background-color: grey}; tr:nth-child(even){background-color: grey}; </style>"; let outsideTable := " <table class = 'outsidetable'> <tr class = 'outsideTR'>" + data.("<td> <table class='insideTable'> <thead> <tr> <th align='center', height='15px'>" + date + "</th> </tr> </thead> <tbody> " + for loop2 in worker do let datacolor := switch loop2.type do case 1: "bgcolor = 'LightGreen'" case 2: "bgcolor = 'yellow'" case 3: "bgcolor = 'orange'" case 4: "bgcolor = 'LightCoral'" end; "<tr class='insideTR'> <td class='insideTD' " + datacolor + ">" + loop2.name + "</td> </tr>" end + " </tbody> </table> </td> "); " </tr> </table> "; html(css + outsideTable)
The big change was I created my own table using JSON. You see this on line 3.
Also added a bit more styling.
-
Wow bill that looks great. I have messed around with this as well, taking your advice and shifting this away from a big mess of choices to a relationship table.
I've been playing around with how best to enter the information and I ended up changing things to a dynamic choice field and a choice field.
Which brings me to a question:
In this part of your CSS it seems like you re-define what each choice color would be
let datacolor := switch loop2.type do case 1: "bgcolor = 'LightGreen'" case 2: "bgcolor = 'yellow'" case 3: "bgcolor = 'orange'" case 4: "bgcolor = 'LightCoral'" end;
is it not possible to do something like:
let datacolor := color(DayPosition)
It would be nice if we didn't have to rely on pre-defined options, like you've been stressing, because there could come a time where my daily work categories could increase (maybe i add a sewist, draper, painter, etc).
I tried a few ways could not seem to get it to work, I have almost zero html/css skills.
I have also tried to get your new code working with my new tables with no success. It wouldn't throw any errors but it would not output any information, so in this database its still with the old formatting
Again thanks for your help
-Buffalo
-
said:
is it not possible to do something like:
let datacolor := color(DayPosition)I didn't even know color() was a command. Thanks for showing me it.
That is a great use of the command. You have to use the raw() command as well, because the raw() command turns the display color into hex color code that html will understand. Now you can get rid of the datacolor all together. It would look something like:
for loop2 in getLaborData[Date = loop1] do "<tr> <td bgcolor = " + raw(color(loop2.DayPosition)) + "> " + loop2.asstName + "</td> </tr>" end + " </tbody> </table> </td> " end;
That is awesome. Love it.
Some other thoughts:
1) in the Labor Breakdown code there is a variable "content" that is not used and can be deleted.
2) Tables Work Days, Production Crew, and Art Assistants could be made children of Production. You do this by setting Composition to Yes in each table.
3) You use a dynamic choice field, Assistant?, in Work Days, may I suggest you remove the '?', this will make is easier for you to use the field in code as you can just type in Assistant. If you left the '?' then you would have to type 'Assistant?' with the single quotes whenever you use the field name.
4) You may want to consider making a Labor Cost table that has each position and the Day Rate. Then in the Art Assistant table you would
a) create another text field called Position
b) create another dynamic field that has a Trigger after update that populates the Position and Day Rate fields.
c) consider creating a discount number field either in Production or a link to a discount field in Estimates.
Keep up the good work.
-
Position is a multiple-choice field.
attached is my recent DB.
Content aside
- 5 mths agoLast active
- 30Replies
- 280Views
-
3
Following