Discussions

 View Only
Expand all | Collapse all

Use field value from one record in a formula field for all other records in same table.

  • 1.  Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 11:50

    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
    ------------------------------



  • 2.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 11:58

    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
    ------------------------------



  • 3.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 12:15

    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
    ------------------------------



  • 4.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 12:21

    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/
    ------------------------------



  • 5.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 12:28

    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
    ------------------------------



  • 6.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 13:01
    Edited by Mark Shnier (Your Quickbase Coach) 12-22-2020 13:25

    I believe that you will need a second relationship so that all of the children are related to Record ID number one in Projects.   

    Try creating a field on the Child Impact Table with a formula value of 1 and create a new relationship back to the project record. Then the look up that field for that summary total down to all Impact records.

    The issue is that you have a value on Project  #1.  But other than that special Impact record, none of the rest of your Impacts children are connected to project  #rom so the lookup form Project  #1 for that Total field  field will be null. (which you proved!).  So we make a second relationship so that every child record is attached ot that special project w/ Record ID# of 1. 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 13:26

    note: I edited my response above for clarity and bad dictation.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 16:53

    Mark,

    Sorry, I'm not following the formula field instructions.  

    I understand I need to make a new formula field in the child table named 'impact'.  Are you saying that the formula is the record id of the record from the parent 'project' table?  If that parent record id is '1', then formula is (1), if that parent record idea is 655, then formula is (655).  Is my interpretation correct?

    Then within the relationship of project<impact, I would create a summary field on the project table for the field described above, and a lookup field of the summary field on the impact table.  Summary function: total, avg, max, min?

    But then what?

    Thanks for your continued help!



    ------------------------------
    J G1987
    ------------------------------



  • 9.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 13:06

    You have a Projects table and a Project Impacts table, with Projects being the Parent to Project Impacts, correct? If that is the case, then you should have Project  #1 having many Project Impact records. The Project Impacts table should have a Numeric field where your users would enter the Impact Value for that particular Impact record. You would then have a Summary field for Impact Value up to the Project table. Assuming that these Impact records reflect certain expenditures for the Project, this would be "Total Project Cost". You can then create a Lookup field from the Project down to the Impacts for "Total Project Cost" so that you would now have two numeric fields on the Impact - Impact Value and Total Project Cost. You can then create a Formula Numeric field on the Impact table to calculate your % of Total Project - [Impact Value]/[Total Project Cost]



    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------



  • 10.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 17:20

    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/
    ------------------------------



  • 11.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 17:41

    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
    ------------------------------



  • 12.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 18:41

    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
    ------------------------------



  • 13.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 18:48

    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
    ------------------------------



  • 14.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 18:53

    Sounds very confusing in your answer above. You are telling us that currently the division is a simple multiple-choice field on the project record.   Copy those choices onto your clipboard and import them into a new table for Divisions. Set the key field of divisions to be the Division name. 

    Then when you make your relationship where one Division has many Projects do not let it create a new field called [Related Division] but rather use the existing multiple-choice field for [Division]. That will change the multiple-choice field from being a simple drop down multiple-choice field to being a selection from a table. 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 21:52

    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/
    ------------------------------



  • 16.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 22:42

    Cool. I never knew that . 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 17.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-22-2020 23:01

    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
    ------------------------------



  • 18.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 10:45

    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
    ------------------------------



  • 19.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 11:56

    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
    ------------------------------



  • 20.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 14:34

    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
    ------------------------------



  • 21.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 14:44

    Ya, I know.  Sorry.  Here they are:

    Project<Impact

    Division<Project

    Division<Revenue

    Year<Impact

    Year<Revenue

    Revenue<Impact



    ------------------------------
    J G1987
    ------------------------------



  • 22.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 14:49

    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
    ------------------------------



  • 23.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 15:04

    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
    ------------------------------



  • 24.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 15:06

    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
    ------------------------------



  • 25.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 15:17

    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
    ------------------------------



  • 26.  RE: Use field value from one record in a formula field for all other records in same table.

    Posted 12-23-2020 19:23

    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.  



    ------------------------------
    J G1987
    ------------------------------