Summarize multiple columns in a chart
I have a single table called "employees" that is updated daily from a csv file with employee data. There is no user input. One of the fields is "age". I need to create a chart that shows: (if "age"...
Forum Discussion
Hi John,
I don't have a step by step tutorial for setting up the relationships I can link you to but I can walk you through the steps in a little more detail.
What I usually start off with in this instance is to create a new table called Master Parent Table. Once that table exists I create a single record just named Master Parent. With that in place I then create a relationship so that Master Parent can have many employee records. This will create for you a new reference field that should be name Related Master Parent in every one of your Employee records that will be blank.
From there you would go into the Employee records you have and you can either manually edit the existing records you have to all relate back to your Master Parent record. Alternatively there is a handy little trick you can do to speed this up if you have a large number of employees. You can go into the field properties for this new reference field and change the field type to a formula - numeric field. Once you do that you can set the formula box up correctly by just putting in the number 1 and then saving the field. Now every record is automatically related to your Master Parent record since it is record #1 in the parent table.
Then what I typically do after this is open up the Related Master Parent field in the Employees table again and switch it back to a standard manual entry numeric field. It will still show all the existing records as related to 1. Then you will set that fields default value to be 1, so that by default all new records are related to Master Parent record 1.
This leave you all set to go into the relationship settings for that table, open up the relationship of Master Parent Record to Employees and add the summary fields you need to the new parent table. This will cause your Master Parent table to have a field for # of records with age over 50, # of records with age under 30, and total number Employee records all in one record that you can then do your division off of. It does take some work to get the structure in place but once done it will maintain itself unless you wish to make changes. I would encourage you if you need more focused help or think a screen share session to go over making a Master Parent record to reach out to the Care team via a support case. You can create a support case here.
I don't have a step by step tutorial for setting up the relationships I can link you to but I can walk you through the steps in a little more detail.
What I usually start off with in this instance is to create a new table called Master Parent Table. Once that table exists I create a single record just named Master Parent. With that in place I then create a relationship so that Master Parent can have many employee records. This will create for you a new reference field that should be name Related Master Parent in every one of your Employee records that will be blank.
From there you would go into the Employee records you have and you can either manually edit the existing records you have to all relate back to your Master Parent record. Alternatively there is a handy little trick you can do to speed this up if you have a large number of employees. You can go into the field properties for this new reference field and change the field type to a formula - numeric field. Once you do that you can set the formula box up correctly by just putting in the number 1 and then saving the field. Now every record is automatically related to your Master Parent record since it is record #1 in the parent table.
Then what I typically do after this is open up the Related Master Parent field in the Employees table again and switch it back to a standard manual entry numeric field. It will still show all the existing records as related to 1. Then you will set that fields default value to be 1, so that by default all new records are related to Master Parent record 1.
This leave you all set to go into the relationship settings for that table, open up the relationship of Master Parent Record to Employees and add the summary fields you need to the new parent table. This will cause your Master Parent table to have a field for # of records with age over 50, # of records with age under 30, and total number Employee records all in one record that you can then do your division off of. It does take some work to get the structure in place but once done it will maintain itself unless you wish to make changes. I would encourage you if you need more focused help or think a screen share session to go over making a Master Parent record to reach out to the Care team via a support case. You can create a support case here.