Need to populate a new field with data from another without having to edit the record

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • In Progress
I have 116171 records so I don't want to do this manually.

I have a formula field where I'm subtracting between 2 other fields. That works, but the result only shows in the new field if I open, edit and save the record. I have 116171 records, I don't want to do that.
Is there another way?
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
  • there's got to be a better way

Posted 5 months ago

  • 0
  • 1
Photo of Danie Grabe

Danie Grabe

  • 92 Points 75 badge 2x thumb
Hi Stephanie,
Could you please post your formula here with the type of all 3 fields so I can try to assist you?
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
The formula works. This issue is populating the field.
Photo of Danie Grabe

Danie Grabe

  • 92 Points 75 badge 2x thumb
Hi Stephanie,

Sorry it is not clear to me. Where are you subtracting 2 fields? Where does [2 HR Notification] come into it?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Stephanie.


If the formula calculates correctly on one record it will work in all 11,000 records.


Perhaps the other 10,999 Record have different data that you expect in those fields.


Do you have a form rule populating some fields which only kicks in when the record is being edited?


If so, what does the form rule do?
(Edited)
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
Yes, it works BUT ONLY if I Edit the record. I don't want to edit over 100k records.
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
I put in the wrong comment before. Please ignore that one.

The formula works but it only calculates/populates the field when i open, edit and save the record. With over 100k records I don't want to do that.

I'd like to find a way to get the new field to update without me having to edit the records.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Do you have form rules in that form which are populating fields? Based on your question, you must.

What is the form rule doing?
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
 I have the following fields
  • Total Count of Holes prev 2w - Numeric Field
  • Total Count of Holes prev 2w Text - Text Field
  • Total Count of Holes prev 4w - Numeric Field
  • Total Count of Holes prev 4w Text - Text Field
  • 4w-2w Total OOS - Formula-Numeric - ToNumber([Total Count of Holes prev 4w Text])-ToNumber([Total Count of Holes prev 2w Text])
I have form rules that write from the Numeric Fields to the Text Fields set up so that I can do the formula that is in the Formula-Numeric Field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
If you have numeric fields, make the formula on the numeric fields.

ie, do the formula on the fields which do not rely on form rules.  Form, Rules are only intended when real like human users edit a single record.
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
The Numeric fields are Numeric Lookup fields. I had to bring them in from another table because they are Summary fields in that table
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
I'm still not understanding why you need the form rules and why you need to be using a formula which depends on data created by a form rule.
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
I don't know that I have this set up the best way. I'm just guessing.

I had to create a summary report in a second table (Product List) to get the data we wanted the way we wanted it. Now I need to compare the data. To do that I brought the summarized data from the second table over to the first table (OOS Data). QB won't let me use them in a formula, I'm guessing because they are numeric lookup fields. So I created the form rules to write the data to a text field. Then I used the text fields for my formula.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Form rules will not work for that so I suggest that you kill the form rule and get the formula working.
Photo of Stephanie

Stephanie

  • 596 Points 500 badge 2x thumb
The formula works. I just need the field to automatically populate. I don't want to edit the records to get it to populate.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
I spoke to Stephanie on the phone and sorted this out.  Stephanie's main issue is that she needed to combine information on multiple records for the same Ticket# into a formula field.  That required setting up a table of Unique Tickets and Stephanie remembering to copy across a summary report of the unique tickets to the Unique tickets table after each data import.