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

Hi Stephanie,
Could you please post your formula here with the type of all 3 fields so I can try to assist you?

The formula works. This issue is populating the field.

Hi Stephanie,

Sorry it is not clear to me. Where are you subtracting 2 fields? Where does [2 HR Notification] come into it?

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?

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.

Do you have form rules in that form which are populating fields? Based on your question, you must.

What is the form rule doing?

 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.

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.

The Numeric fields are Numeric Lookup fields. I had to bring them in from another table because they are Summary fields in that table

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.

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.

Form rules will not work for that so I suggest that you kill the form rule and get the formula working.

The formula works. I just need the field to automatically populate. I don't want to edit the records to get it to populate.

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.