ContributionsMost RecentMost LikesSolutionsCalculated field spanning records on multiple tables I have two tables currently: Projects and Tasks (the Projects table being the parent). Only one person can be assigned to a project but a project can have multiple tasks. Each task also has an "Assigned To" field. Employees can be assigned to either the parent project or child task or both and not every project has a task. I am trying to calculate a "total duration" across the projects and tasks table. I have a Total Duration field for each project record and a Total Duration field for each task record. I'm trying to figure out how to blend this data so I can calculate this across each team member assigned either a task or project. The following rules would apply: If user is not equal to user in the Assigned To field on the project table and user is equal to user in the Assigned to field on the tasks table, then value = Total Task Duration; If user is equal to user in the Assigned To field on the projects table, then value = Total Project Duration. If user is equal to user in the Assigned To field on both the projects table and the tasks table, then value = 0 (since they will already be getting credit for the duration associated with the project) My inclination was some sort of skinny table with a pipeline that creates a record for each task and each project, including lookups to the Assigned To fields. Then we would use that table to calculate this duration total on these blended records. Has anyone had to do something similar? Does my solution sound like the most efficient? If you made it this far, thanks for any thoughts you may have! ------------------------------ Jen Black ------------------------------ Dynamic multiple choice options based on value from another field I have two multiple choice fields on the same table. Each record has a required field called "Program Type," and an optional field called "Scope." There are three program types that will use the scope field, the rest won't see this field when viewing or editing these records on the form. Each program type has a different set of choices they can use within the "scope" field. I would like a way for the form to show only the appropriate options to when the "program type" field is set to these unique values. I am still using old forms on this table. Example of what I would like the drop down to show based on program type. Program Type 1 Scope A Scope B Scope C Program Type 2 Scope D Scope E Scope F Hopefully that's clear, thanks for any advice you may have! Jen -- I know I have done this other places in my sy ------------------------------ Jen Black ------------------------------ Re: Percentages are Sums instead of Averages on the Total and Averages Rows Thank you! I had completely glanced over that option so many times. And yes, thank you for clarifying. I have a separate field on a different table calculating the more accurate averages based on the volume of child records within each percentage. Appreciate the quick response! ------------------------------ Jen Black ------------------------------ Percentages are Sums instead of Averages on the Total and Averages Rows Currently percentages are sums instead of averages on the "Total and Averages Row" for my table reports. Having an average of my percentages but a total of my other numeric data is what I am looking for in this line. Is there a way for %s to be averaged on that final row instead of totaled? Screenshot for reference. Thanks in advance! ------------------------------ Jen Black ------------------------------ SolvedRe: Include a report table in a notification email Hey! It took me a minute to find. So insert the "A copy of the changed record" marker into the body of the notification. Then below the body of the notification, use the "data form" drop down to select the "mini form" you created. I also took out the edit/view options on the report i linked to in my mini form, I found it made the table look cleaner in the final email. Hope that helps! Jen ------------------------------ Jen Black ------------------------------ Re: Include a report table in a notification emailHey Mark, thanks for the input. It worked like a charm! ------------------------------ Jen Black ------------------------------ Re: Include a report table in a notification emailHi Mark, I'm resurrecting this thread a bit to see if this "mini form" solution is still available. I don't see this listed in my Markers for my notification. Is there still a way to accomplish this? Thanks for any information you can provide. Jen ------------------------------ Jen Black ------------------------------ Metro Area FieldI am trying to figure out a good way to simplify user searching for client headquarter locations. I currently have an open text field allowing users to enter HQ location, however this allows a lot of discrepancies in the way users are entering data. One example of this could be entering data for Kansas City could look like this: KCMO Kansas City, Missouri Kansas City, MO Kansas City I don't want to have an entire address field because we don't need it and it takes up too much space on my form. One solution I thought about was to create a multiple choice field populated with most major cities and states our clients reside in, but allowing users the option of adding choices so we would at least have some consistency in formatting but I wanted to throw the idea out to the community and see if anyone else had run into/solved this problem as well. Thanks, Jen ------------------------------ Jen Black ------------------------------ Re: Pipeline views {{e.id}} as the text "{{e.id}}" rather than the record ID numberI'm not sure if this will help but for all of my pipelines, I end up turning my record ID field into an integer in the pipeline Query step by adding "|int" into the field as seen below. SS also provided. Not sure if that will help but I've never had any issues with pipelines on the record ID side with this trick. {{a.id|int}} ------------------------------ Jen Black ------------------------------ Re: If Formula for Null valuesBrilliant! This worked. Thanks, Mark! Jen ------------------------------ Jen Black ------------------------------