Formula to number a line item to create an order 1,2,3 etc.
I am creating a module that will be a template to import into our ERP. One of the line items is called 'Journalnum' which essentially needs to start at 1 and every line thereafter will be 2, 3 4, etc. E.g. in excel cell A1 is journal number 1 and cell A2 will be a formula to equal A1+1. There are 1000+ lines in the template.
1) Use pivot / filter / show / hide in order to display your module in the above arrangement, but without the RowNumber dimension. Save this View as <Module name> - Staging View"
2) Create a numbered list named "RowNumber". - Add properties (format: list) named for each dimension (Product, Customer, Store) in the final output. - Add a property (format: Number) named "List_Rank_Num" and give it a RANK formula. - Add another property (format: Text) named "RowNum" and with a formula
TEXT(List_Rank_Num) - Set the Display Name property of the RowNumber list to "RowNum".
4) Import from your saved "... Staging View" from step 1 into the "RowNumber" list with each item identified as unique by dimensiona Product, Customer, and Store. Each item (corresponding to a row of the desired output) will get a serial number assigned by the system as its Name. (Be sure to clear the list before each time you repeat this process.)
5) Create a new module name "CSV Output" with one List dimension: RowNumber
Add the following Line Items:
Product (format: List, Product) formula = RowNumber.Product