Ok, thanks for the help.
It is something with the lookup and lookdown between the 2 tables 'revenue' and 'impact'.
I should be able to lookup a formula field from one table and use it in a formula in another table, right? Thereby always having the same denominator.
Original Message:
Sent: 12-23-2020 15:16
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
I think that someone is going to have to have a look at your app to determine the issue. Either the Care Team or contact me directly by email.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-23-2020 15:05
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
What is the purpose of this relationship
Revenue<Impact
Answer: I want to use the formula field from Revenue and use it in a formula in Impact. Formula: Impact YTD/Revenue YTD. Where Impact YTD is a formula field in the impact table, and Revenue YTD is a lookup field coming from a formula field in Revenue table. Then, each record, or each project impact, would show the % it is contributing to overall YTD revenue.
I have a formula field in this table to sum the monthly revenues into a 'YTD Field'. Since each record is specific to a year, this works fine.
What table is "this" table. Also When you say a formula field did you mean a summary field?
Answer: This is the Revenues table. And YTD Field is a formula field within the Revenues table. [Jan Actual]+[Feb Actual]+[Mar Actual]+[Apr Actual]+[May Actual]+[Jun Actual]+[Jul Actual]+[Aug Actual]+[Sep Actual]+[Oct Actual]+[Nov Actual]+[Dec Actual]
This YTD Field in the Revenues table is a lookup field in the Impact table. Trying to use it in the formula: Impact YTD/Revenue YTD, described in the first answer on this post.
------------------------------
J G1987
Original Message:
Sent: 12-23-2020 15:04
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
What is the purpose of this relationship
Revenue<Impact
Answer: I want to use the summary field from Revenue and use it in a formula in Impact. Formula: Impact YTD/Revenue YTD. Where Impact YTD is a formula field in the impact table, and Revenue YTD is a lookup field coming from a formula field in Revenue table. Then, each record, or each project impact, would show the % it is contributing to overall YTD revenue.
I have a formula field in this table to sum the monthly revenues into a 'YTD Field'. Since each record is specific to a year, this works fine.
What table is "this" table. Also When you say a formula field did you mean a summary field?
Answer: This is the Revenues table. And YTD Field is a formula field within the Revenues table. [Jan Actual]+[Feb Actual]+[Mar Actual]+[Apr Actual]+[May Actual]+[Jun Actual]+[Jul Actual]+[Aug Actual]+[Sep Actual]+[Oct Actual]+[Nov Actual]+[Dec Actual]
This YTD Field in the Revenues table is a lookup field in the Impact table. Trying to use it in the formula: Impact YTD/Revenue YTD, described in the first answer on this post.
------------------------------
J G1987
Original Message:
Sent: 12-23-2020 14:49
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
Two questions.
What is the purpose of this relationship
Revenue<Impact
Can you explain this further?
I have a formula field in this table to sum the monthly revenues into a 'YTD Field'. Since each record is specific to a year, this works fine.
What table is "this" table. Also When you say a formula field did you mean a summary field?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-23-2020 14:43
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Ya, I know. Sorry. Here they are:
Project<Impact
Division<Project
Division<Revenue
Year<Impact
Year<Revenue
Revenue<Impact
------------------------------
J G1987
Original Message:
Sent: 12-23-2020 14:34
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
I am now somewhat lost on all your different tables in their relationships.
Can you provide a list of all your tables and how they are related?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-23-2020 11:56
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Hi Mark,
Thanks. Still strugglin :)
Here is where I'm at:
New projects are entered into project table. The user selects a division name, which is pulling from the division table.
$ rev generated by the project is entered into the impact table every month. Many years can be added to one project. The user selects the add impact button and a project from the related project field is auto populated, this related project selection auto populates the related division. The user selects the year, the year choices are coming from the 'year table'. I have a formula field in this table to sum the monthly impacts into a 'YTD Field'. Since each record is specific to a year, this works fine.
The Total Revenue table is used to log all revenues for each division for each year. The user selects the division, which is pulling from the division table, the user selects the year, which is coming from the year table. The user enters $ into month by month throughout the year. I have a formula field in this table to sum the monthly revenues into a 'YTD Field'. Since each record is specific to a year, this works fine.
I have created a summary field on the total revenue table which sums up the YTD field from the impact table. I made this summary field a lookup field on the impact table.
I have made the Total Revenue YTD Field a lookup field on the Impact table. PROBLEM: I am trying to use this in a formula field on the impact table (for each record) and it is not working. [impact YTD/summary field revenues YTD]
I have made a summary report on the Total Revenue table. PROBLEM. One of the fields that I am summarizing is the summary field on the total revenue table which is summarizing YTD impacts from the impact table. Nothing is populating. Treat blanks as zeros is on.
------------------------------
J G1987
Original Message:
Sent: 12-23-2020 10:45
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
Rather than hard code a bunch of field which then need to be maintained each year, I suggest a better approach.
Make a summary field of the total impact in the current year. That one is pretty obvious how to set up because you can do it where the date of the impact is during the current year.
That one is pretty obvious how to set up because you can do it where the date of the impact is during the current year.
Then make for additional fields for the summary of the total impact in the previous1st, 2nd, 3rd, 4th, 5th years So for example the total impact in year three is where the date of the impact is during the previous three years and also is not during the previous two years.
Once you have those fields look them all down to the impact table.
once you have those fields look them all down to the impact table.
Then make a formula to determine which summary field look up to use for the percentage calculation. I can help you with that formula if you need help. Just post back.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-22-2020 23:01
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
That is very cool, Blake. Thanks. I had some data clean up to do, as the 'division' drop down was a multi select text versus a multi choice. Wouldn't allow it to be a reference field as that type. Once I made the change to multi choice your instructions worked, Mark. The drop down in 'projects' is pulling from the division table.
So, I'll have a project table, an impact table, a division table. Do you recommend I now add another table to house the total revenue for the division? Keeping in mind what I want is to compare a single project's $ YTD project impact to the division's overall YTD revenue.
The additional layer is that I need to represent this for every unique calendar year. Every year, new actuals will be added to impact and to total revenue. Do I need a 'year' table, as I'll need to tag the 'impact' with the year and the division's total revenue with the year.
------------------------------
J G1987
Original Message:
Sent: 12-22-2020 22:41
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
Cool. I never knew that .
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-22-2020 21:52
From: Blake Harrison
Subject: Use field value from one record in a formula field for all other records in same table.
To make the Division a table now, from your report, you can select the drop-down on the Division header and select "Make a Table from This Column" and Quick Base will make the table for you, including the relationship, and none of your reports will be impacted.
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quick Base Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
Original Message:
Sent: 12-22-2020 18:48
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Yes, currently division is handled with a drop down field of the choices. I wish I would have made it a table from the beginning. I will give it a try with the multiple choice division field, but when I've tried that previously every associated project appears in the look up field, versus just the 4 divisions.
Thanks again.
------------------------------
J G1987
Original Message:
Sent: 12-22-2020 18:41
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
Now I don't think my previous answer applies because I don't think I understood what you were trying to do.
If you have four Divisions and you're trying to make totals roll up to those four Divisions I would set up a new table for Division. Make the key field of the table be the words for the Division name.
Make a relationship to Projects make based on what presumably is currently a multiple-choice Division field. Then summarize your totals up to Division and look up the totals down to the Projects.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-22-2020 17:41
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Blake, Yes, thanks, I understand. Ideally we would have set up the app as you described. However, we are trying to achieve this additionally functionality in an already existing app, and what I described above will be repeated for 4 business divisions, which is the real problem. So, ideally we would have a 'divisions' table, followed by the tables you described. We could go back and do that now, but then we'll have to re-do all the reports. So, this is why I was trying to doing it by adding a 'project' that represented total impact (revenue) for the division. Everything works doing it in this workaround fashion, except for the problem I described.
------------------------------
J G1987
Original Message:
Sent: 12-22-2020 17:19
From: Blake Harrison
Subject: Use field value from one record in a formula field for all other records in same table.
Typically speaking, when setting up your table structure, you would not want to have a record on a table represent the total of a field that is also on that same table. In your example, you have Projects and Impacts. Based on your description, you are wanting to represent the total of all Impact records on the first Impact record. This is not an ideal way to setup your application.
To properly calculate the total of a field across all records on a table, you need to have a Parent table above that and create a Summary field for the field you want the total of.
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quick Base Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
Original Message:
Sent: 12-22-2020 12:27
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Yes, but... 'Total Project' is represented by 'record 1' in the table. So, yes, % of 'record 1' that the Impact Value of each other individual record represents
------------------------------
J G1987
Original Message:
Sent: 12-22-2020 12:21
From: Blake Harrison
Subject: Use field value from one record in a formula field for all other records in same table.
With "Z", are you trying to calculate the % of Total Project that the Impact Value represents?
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quick Base Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
Original Message:
Sent: 12-22-2020 12:15
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Hello Mark,
I had not had the summary field for 'field x' checked as 'treat null as zero'. I have since checked that box, but no change. Still blank values coming back for that 'summary field of 'x''.
The 'X' field does in fact have a value for 4 of the records in the table.
Thanks for the help.
------------------------------
J G1987
Original Message:
Sent: 12-22-2020 11:57
From: Mark Shnier (YQC)
Subject: Use field value from one record in a formula field for all other records in same table.
For this issue here
I have created the summary field for field 'X'. I am getting null value.
Have you set the field properties of that Summary field to "treat null as zero". What is the value of that field in the "other" records. Do they all calculate to null or to zero?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
Original Message:
Sent: 12-22-2020 11:49
From: J G1987
Subject: Use field value from one record in a formula field for all other records in same table.
Project Table < Impact Table
Related Project Table Record #1 in Impact table has a formula field 'X' that results in a currency value. (the other records have this field too, but purposely have no value)
All other related Project Table Records in Impact table have a formula field 'Y' that result in a currency value (the record #1 has this field too, but purposely no value)
What I want to do is create field 'Z' in project impact that has a formula of [Y/Sumof X], to result in a % value. I have created the summary field for field 'X'.
I am getting null value.
I've tried to relate a 3rd table to make it work, where both Project table and Impact table are parents of 3rd table. But, still won't work.
------------------------------
J G1987
------------------------------