How to get all matching values from table and increment the value

Help Studio
, ,
1

Hello friends i have a table which has invoice number 123, 123-1, 123-2, 123-3. First thing i have to do is to check whether 123 is present, if i just filter using 123 it just gives value 123 row index. I also want to know the last value that is 123-3 or any other number after - so that i can add next number and make it as i want, how to get filter the last value and add next value as 123-4.

Thanks in advance
image

2

Try regex or use 123 as prefix

3

@aslam_ali1

  1. Read the table data and store it in a DataTable variable, let’s call it “dataTable”.
  2. Use the Select method of the DataTable to filter the rows based on the pattern “123” in the invoice number column. This will give you an array of DataRow objects that match the filter condition.

makefileCopy code

filteredRows = dataTable.Select("InvoiceNumber LIKE '123%'")
  1. To get the last value in the filtered rows, you can use LINQ to sort the rows based on the invoice number column in descending order and retrieve the first row.

scssCopy code

lastRow = filteredRows.OrderByDescending(Function(row) row("InvoiceNumber")).FirstOrDefault()
  1. Once you have the last row, you can extract the invoice number and increment the last number by 1 to get the next value.

mathematicaCopy code

lastInvoiceNumber = lastRow("InvoiceNumber").ToString()
nextInvoiceNumber = $"{lastInvoiceNumber}-" & (Integer.Parse(lastInvoiceNumber.Split("-"c)(1)) + 1).ToString()

The above code assumes that the last value is always in the format “123-X” where X is a numeric value after the hyphen.
5. You can then use the “nextInvoiceNumber” value as needed, such as adding it to a new row or performing any other operations.

4

@aslam_ali1

If you want to do it on excel directly

Then try this

  1. Use a do while loop with condition as true and in the properties assign a variable indexvar of type integer to the index property,also for safety add a max iteration
  2. Use find/replace activity with foundcell as output variable and value to search for as the inv number 123-(indexvar+1).ToString
  3. Use if with Not (IsNothing(Foundcell) OrElse String.IsNullOrEmpty(FoundCell)) and on then side use assign with foundindex = indexvar+1 also another assign to save the foundcellvar = foundcell and on else side add break
  4. Now after the loop you have the last index where it is found and the last cell number as well…first check using if …if the foundindex value is >0 only then it is found
  5. Now use insert row activity and use the foundindex to insert a row
  6. After that use write cell and use foundcell and add the value needed.make sure to increment the number by 1 system.Text.RegularExpressions.Regex.Match(foundcellsvar,"\D+").Value + (Cint(system.Text.RegularExpressions.Regex.Match(foundcellsvar,"\d+").Value)+1).ToString

Cheers

5

Thanks for replying
no im doing it for data table in studio

6

@aslam_ali1

Then also you can use same approach…

Istead of find use look up datatable

Insteqd of insert row use insertat method

Cheers

7

@raja.arslankhan
Thanks you so muchhhhhhhh
It was a wonderful solution. thanks a lot man.

to get the exact o/p what i needed i used below code for last line which you mentioned in step4.
nextInvoiceNumber=lastInvoiceNumber.Split(“-“c)(0).ToString+”-”+(Integer.Parse(lastInvoiceNumber.Split("-"c)(1)) + 1).ToString()

because from that code i was getting o/p as 123-3-4

8

@aslam_ali1 give me few moments. I will share

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.