0 Replies Latest reply: Sep 28, 2017 5:08 AM by mike lees RSS

    Help with AutoNumbers scripting

    mike lees

      So I have table containing a list of products per customer and a status

       

      What I want to is count by status and customer by wanted a rolling total.

       

      Example Raw Table

       

         

      AccountKeyCumulativeInstallDateNumberCategoryCategoryFullCategoryPEO
      96609|PANTHER110/27/20134397205FULL4397205
      96609|PANTHER21/26/20144397593FULL4397593
      96609|PANTHER34/27/20144577138FULL4577138
      96609|PANTHER46/28/20155117108PEO 5117108
      131542|PANTHER111/22/20155160542FULL5160542
      131542|PANTHER210/23/20169294480PEO 9294480

         

       

      Required result

       

       

         

      AccountKeyCumulativeInstallDateNumberCategoryCategoryFullCategoryPEOFullCountPeoCount
      96609|PANTHER110/27/20134397205FULL4397205 10
      96609|PANTHER21/26/20144397593FULL4397593 20
      96609|PANTHER34/27/20144577138FULL4577138 30
      96609|PANTHER46/28/20155117108PEO 511710831
      131542|PANTHER111/22/20155160542FULL5160542 10
      131542|PANTHER210/23/20169294480PEO 929448011

       

       

       

       

      I've been trying to use AutoNumbers   but I couldn't get it do work

       

      autonumber(alt(Number,'NA'),AccountKey)-1 as Serial

      ,autonumber(alt(CategoryFull,'NA'),AccountKey)-1 as SerialCategoryFull
      ,autonumber(alt(CategoryPEO,'NA'),AccountKey)-1 as SerialCategoryPEO

      example

       

      AccountKeyCumulativeInstallDateNumberCategoryCategoryFullCategoryPEOSerialCategoryFullSerialCategoryPEO
      96609|PANTHER110/27/20134397205FULL4397205 10
      96609|PANTHER21/26/20144397593FULL4397593 20
      96609|PANTHER34/27/20144577138FULL4577138 30
      96609|PANTHER46/28/20155117108PEO 511710804
      131542|PANTHER111/22/20155160542FULL5160542 10
      131542|PANTHER210/23/20169294480PEO 929448002