Skip to main content

HI

IMAGINE A RECORD…..PRIME = DRAWING 1234, MATERIAL = ABCD, LEVEL = 1 AND QTY = 5.

NOW IMAGINE ANOTHER RECORD - SAME DRAWING AND MATERIAL, LEVEL = 2 AND 10 = 10

 

I AM USING QTY TO DO A TOTAL IN ANOTHER TABLE - AND IN THIS CASE IF I DID THAT I WOULD GET 15….WHAT I NEED TO DO IS TO FIGURE OUT A FORMULA, AUTOMATION OR ROLLUP ETC TO ACTUALLY SAY THAT I WANT THE THE QTY FROM ANY RECORD THAT HAS SAME DRAWING AND MATERIAL BUT ONLY THE HIGHEST LEVEL…..IN THIS CASE THE 10 ON LEVEL 2….AND THE 5 SHOULD DROP TO 0 (USING A NEW QTY DATAFIELD THAN THE ORIGINAL QTY).

IF ANOTHER RECORD COMES IN WITH SAME DRAWING AND MATERIAL BUT NOW LEVEL 3 QTY 20….THEN THE RECORDS WITH 5 AND 10 SHOULD BOTH DROP TO 0….AND I COUNT 20 FOR ALL 3 RECORDS IN THE ROLLUP...AND SO ON…..

 

LOOKED AT ARRAY FUNCTIONS BUT DO NOT SEE ANYTHING THERE I COULD USE AND CREATING A LOOKUP LOOPING ON ITSELF DID NOT SEEM TO GIVE ME A BASIS FOR GROUPING THE 3 RECORDS BUT SEEING COUNTS OF 0, 0, 20….

 

ANY IDEAS?   THANKS IN ANTICIPATION     MIKE

 

Hm I think you’d need a table where each record represented a single Drawing and Material link, e.g.:

The ‘Display’ formula field has this:

REPT(
  '0, ',
  {Count (Table 1)} - 1
) & {Highest Level Quantity (from Table 1)}

And I've set it up here for you to check out

---

If you actually want the records in Table 1 to have their ‘Quantity’ values set to 0 we’d need to use an automation for that though.  It didn’t seem like we’d want that though; was thinking we’d want to keep that historical data?  Lemme know if that’s a thing and I’ll set that up for you

 

super (Thanks again) Adam…..let me try that out tomorrow….Best regards   Mike SW London

Terms & Conditions