ContributionsMost RecentMost LikesSolutionsRe: Design Recommendation Needed Let me answer in parts. The Estimate is made up of about 200 different costs i.e. License Fee A, License Fee B, Labor Cost A, Labor Cost B, Training Cost A, Training Cost B. Each one of these 200 different costs is made up of some combination of user input item (# of Locations, # of Users etc) and unit cost of the specific item (License Fee A or B etc). Each unit cost can change at any time. For example a vendor changes their price on License Fee A. Without a way to freeze all the values in the completed estimates, updating the unit cost for License Fee A will change that cost on previously completed estimates. The ideal situation would let me create a rule to freeze all values in a record once it is finalized and then I can update the existing fields as needed and the previous records would not update but I don't think that is possible. The customer has a requirement that all previously completed Estimates remain in the same table. I will have to do some research on Summary fields as that isn't something i have done before. Design Recommendation Needed I have a table used as a Cost Estimator. The user fills out values in a form, and the table outputs a series of costs based on formula fields. There are some 200 individual costs that are calculated based on the user's input. For example, a vendor license cost is calculated by # of Users times the Unit Cost of a license. The # of Users is user entered, and the Unit Cost is hardcoded into the formula. Example, [# of Users] * 1.50 where # of Users is entered in the form and 1.50 is the current unit cost of the license. This works as long as the unit costs don't change. Once the unit cost changes, it changes the value of the field for all historical Records in the table which is inaccurate. The two routes to preserve the integrity of the historical records while allowing new records to be created with new unit costs are to utilize snapshot fields which will freeze a field value, or look up the unit cost from another table every time the record is opened based on a date in the record. Here is where I am running into trouble. For the snapshot method, I don't know how to make the field default to a specific record in the Unit Cost Table. For example, I have 200 unit costs in a table. I need to default each of those unit costs into their own fields in the form. It is not up to the end user to choose which Units they want. For the look up the unit costs from another table every time, I don't know how to make the formula look to the right version of the unit cost in the reference table based on the date in the Estimate. For example, the Unit Cost Table has 3 records for Vendor License Cost. One Record is the unit cost for 01/01/2022-01/01/2023, the second record is the unit cost for 01/01/2023-01/01/2024 and the final record is Current Cost. Depending on the date the Estimate was completed it should pick the appropriate unit cost. So if the Calculation was done in June of 2023 it should go to the table and get the correct unit cost. I am happy to share any examples or any new detail. I am totally redesigning this tool so I can start from scratch. Re: Table to Table Relationships Criteria I think Report Link is the solution i was looking for. Having the tasks copied would be a dissatisfier for my staff. But I do want a way to see that information at the location level. I will build that out and go from there. Thanks for the recommendation. ------------------------------ Nick Bellows ------------------------------ Table to Table Relationships Criteria I have two Tables. Table 1 is a list of locations. Table 2 is a list of tasks. Some tasks apply to a specific location and some tasks apply to all locations. I have a table to table relationship set up (1 Location can have many tasks) and can view the Location Specific tasks under the location record in Table 1. I am not sure how to get the tasks that apply to ALL Locations to show up under each Location record in table 1. ------------------------------ Nick Bellows ------------------------------ Field Help Text ExportHello, Novice API user, Pipelines only has access to Quickbase Channel. I have 3 tables that began as copies of an original table. 250 fields each. Over time they have been modified and are now slightly different from each other. I have been asked to review the Field Help Text Values of the fields that are named the same across all 3 tables. I am trying to pull the Field Name and Field Help Text into a CSV for the purposes of comparing values. Any guidance would be greatly appreciated. ------------------------------ Nick Bellows ------------------------------ Re: Conditional Look Ups across Tables in 1 AppMy concern is scale. My example is a much scaled down version of my actual situation. In reality I have something like 50 positions and will eventually have dozens of cohorts. I don't want to the field to be selectable from a list of Managers to choose. I want it to populate with the name for that position in Table 1. I am starting to think that because of the conditional nature i.e. Task 2 is assigned to Cohort B and the owner of that task is Manager, I need to create a query to the First Table. Not sure I know how to do that. ------------------------------ Nick Bellows ------------------------------ Conditional Look Ups across Tables in 1 AppI have a table with a record for each Cohort. Within a Cohort Record are fields for identifying the Director, Manager, Analyst assigned to that Cohort. For example: Cohort A; Director=Tom, Manager=Jane, Analyst=Mary Cohort B; Director=Angela, Manager=Glen, Analyst=Stef I have a second table with tasks by position. For example: Task 1; create kickoff presentation; Director Task 2; Schedule presentation; Manager Task 3; Present; Analyst In the second Table, when I select the Cohort for a task, I want to pull in the appropriate person based on the Position that owns the task. For example: I assign Cohort A on Task 1. Since the Position is Director I want to pull in Tom. I assign Cohort B on Task 2. Since the Position is Manager I want to Pull in Glen. I have created a table to table relationship that allows me to Choose the Cohort for the task in Table 2 from Table 1. But I can't figure out how to make the person field populate based on the combination of Cohort and Position. In reality I will have more than 50 positions and the people in those positions can change from Cohort to Cohort. I suspect there is some sort of Get Values type formula needed here but I am stumped. Any guidance would be appreciated. ------------------------------ Nick Bellows ------------------------------ Create Field to Show Status of Another Record in DatabaseI have field that is used as a predecessor field that contains the Record ID of the predecessor record. I would like to create a field, or a color highlight of the predecessor field to show the status of that predecessor record. Record ID 10 has predecessor of Record ID 5. I would like to display the Status Field of Record ID 5 on Record ID 10. ------------------------------ Nicholas Bellows ------------------------------ Copy Button For Rich Text Formula Source FieldHello, I am trying to create a Copy Button field in my report based on the code below that was found in this thread. The issue I am running into is the source field is a Rich Text Formula field. When I use the code below it copies to the clipboard but in plain text with the HTML tags. For example the field I want to Copy contains: Begin Task XXX Description Text Here Start Time: MMDDYY HHMM but what ends up on the clipboard is <b>Begin</b><br><b>Task</b> XXX<br>Description Text Here<br><b>Start Time:</b> MMDDYY HHMM Here is the code for the button: var text CB= URLEncode([Teams]); var text Alert= URLEncode("Activity Details" & " were Copied to the Clipboard"); "<a class='Vibrant Success' style=\"border:0px solid green; background-color:green; text-align: center;\"href=" & "javascript:{" & "navigator.clipboard.writeText('" & $CB & "').then(function(){" & "},function(){" & "});" & "$.jGrowl('" & $Alert & "',{theme:'jGrowl-green'});" & "};" & ">Copy to the Clipboard</a>" Is the problem the Button Code? Or is the problem my simplistic coding of the Rich Text Formula field to make it look the way I need it to look? Not a coder, just looking for a quicker way to copy this data instead of highlighting, right click, copy, paste in the browser. ------------------------------ Nicholas Bellows ------------------------------