Help

Marking if entry is newest iteration

Topic Labels: Automations Data Formulas
Solved
Jump to Solution
400 4
cancel
Showing results for 
Search instead for 
Did you mean: 
vvakharia
4 - Data Explorer
4 - Data Explorer

Hello! Here's my situation.

I have a table 'Part List' that is automatically updated with parts from an external base that I do not control. One of the data entries inputted is "version" and "iteration". So I could have the following table:

Part #VersionIteration
001A1
001A2
001B1
002A1
003A1
003A2

Where the rows in red are the most recent parts (red is only shown here for emphasis, not in airtable). Deleting rows is not an option because I do not control the data source. I want to create a column that will have checkboxes, and it will be checked if it is the most recent iteration.

Dedupe does not work because I cannot merge cells that I cannot edit, and I have thousands of entries so that would take too long.

I've tried to adapt the Mark Duplicate example but I get the "Api request failed: Request Entity Too Large" error. But also I wouldn't know where to go from there.

The closest I've gotten is below where I can mark a checkbox in a "Is Duplicate" column if it is some iteration of a duplicate part number, but not if it's the newest or not. Where do I go from here? (wc number = Part #)

vvakharia_0-1701375081066.png

 

1 Solution

Accepted Solutions
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
Deduplicate is a wrong way.
create linked field to a New table. copy whole primary field there. make automation that will update each new non-linked record (for example 'When record matches condition Part is not empty and Link is empty" => update Link with value from Part.)
Now create a formula field (Formula1) which can be compared to get a maximum value in a group. Maybe you can just add 'Modified time' for that case, but if that not works, do something like 
100*FIND(Version; 'ABCDEFGHIJKL')+VALUE(Iteration)

In New table, you get a unique list of Part# with a lot of links. Add rollup field MAX(values) from your Formula1, to get maximum value for each of Parts. 

Alexey_Gusev_0-1701392817469.png



Now return to Table1, add Lookup and choose this rollup from Table2. The rest is to create another formula with  IF (Lookup=Formula1, ' ✔ ' ). 

Alexey_Gusev_1-1701392891217.png

 

See Solution in Thread

4 Replies 4
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
Deduplicate is a wrong way.
create linked field to a New table. copy whole primary field there. make automation that will update each new non-linked record (for example 'When record matches condition Part is not empty and Link is empty" => update Link with value from Part.)
Now create a formula field (Formula1) which can be compared to get a maximum value in a group. Maybe you can just add 'Modified time' for that case, but if that not works, do something like 
100*FIND(Version; 'ABCDEFGHIJKL')+VALUE(Iteration)

In New table, you get a unique list of Part# with a lot of links. Add rollup field MAX(values) from your Formula1, to get maximum value for each of Parts. 

Alexey_Gusev_0-1701392817469.png



Now return to Table1, add Lookup and choose this rollup from Table2. The rest is to create another formula with  IF (Lookup=Formula1, ' ✔ ' ). 

Alexey_Gusev_1-1701392891217.png

 

 

Is there a system of versions and iterations? Like, every version goes through x iterations and then gets bumped up to the next version?

If yes, then you can SWITCH() the version with a number and then add the version and the iteration together and the highest number is the newest.

pressGO_design
9 - Sun
9 - Sun

@Alexey_Gusevand I cross-posted - I like his solution better than mine 😁😁

Thank you!!! This did the trick. Only thing is that "automation that will update each new non-linked record", I could not figure that out and make it do anything. But what I needed was done without that automation.