Auto filling quote number column using automations?

juliesusannee

I want my quote number to auto populate a quote number only when a new job row (parent row) is added. We use the quote number NV25-0xxx (ex: NV25-0258, NV25-0259). Multiple clients requesting the same job go in children rows, the children rows get the same number as the parent row so I do not need new quote numbers(I added a screenshot showing this).

So, I was thinking it would be perfect to use the automation feature to add the quote number only when parent rows are added. So, what formula would I use in the change cell value box?

One thing is that once quote numbers get assigned, they don't stay in order on the sheet as things adjust and bid dates change but we still use the same quote number. So that's where i get lost on the formula because I don't know how to word it in a way that the system won't get confused?

Hopefully that makes sense, and someone can help me out!

prime_nathaniel

This issue is mostly due to utilizing hierarchy in place of discrete normalized objects in your data model. While this is a common use case it does have a trade off. One trade off is going to be auto number cannot apply to both parent and child type records with bespoke logic / rules (ie adding a child has the same auto number effect as adding a parent because its one column shared between the obbjects).

Not sure how you are updating / filling out the job request, but it is possible to copy a "job" to another sheet via automations that is just for JOBS and that job sheet has an auto number column on it. So only jobs now are getting auto number. You would the datamesh or index match that number back over to your original sheet.

If you needed child rows to have their own job number system I'd figure you could do the same approach, now you have 2 separate number systems which is more ideal for data modeling (each bespoke object ideally has its own unique key).

juliesusannee

@prime_nathaniel I have a parent row check box column though, as you can see to the far left. So far in most situations this helps when I've set up automations because only my main job row has the box checked. The children rows have the box unchecked. So, in the automation i was trying to set up, i have "when a new row is created and the parent row column box is checked, only then fill out the quote number column cell" so i just need a formula that will keep track of the quote number? or is that still not possible this way?

prime_nathaniel

It is not possible that way as you cannot generate auto numbers on JUST parents. Well unless you want to use Bridge then you can via the API. To do it entirely with core your options are apply auto number to every row OR you have to maintain a job sheet database with its own autonumber then a separate child sheet database if you want them to have their own number.

Automations do not have access to auto numbering or unique numbering formulas. But what they can do is COPY rows and then the sheet it copies to DOES have the ability to auto number as its own column.

Assuming your job names are truly unique and do not change there is no impact to your current sheet other than getting the functionality you as asking for.

juliesusannee

@prime_nathaniel Okay so I created an additional sheet to auto populate my quote number but now i need to bring that number back into my sheet. . this works by using the formula

=INDEX(COLLECT({AUTO POP NUMBERR}, {JOB NAME}, [Job Name]@row), 1)

However, I obviously don't want to type this in every time, I need it as a column formula but this is a sheet with data going back several years and I don't want to interfere with the previously entered information, only the new(blank rows) when added. So, I had come up with below. . but now am getting a Circular Reference error.

=IF(ISBLANK([Quote Number]:[Quote Number]), INDEX(COLLECT({AUTO POP NUMBERR}, {JOB NAME}, [Job Name]@row), 1), [Quote Number]@row)

Any chance you see how I could fix this?

prime_nathaniel

@juliesusannee

Option 1 is to use datamesh to do it.

Option 2 is you duplicate all past data to the same sheet overriding the auto number for those records and now you are targetting the final resolved column

Option 3 you remap your original sheet to do the override, the lookup does the auto number from your auto number sheet, if no auto number is found then use the override column (your old existing column).

Overrides work by having one column with an formula, one number with a standard text number, and then a resolved column where if override = blank you use the auto number, if override exist you use the override value