Values in the Product column and corresponding code:
Products with value 1 will have result code: CKD-CN- (quantity: 1)
Products with value 2 will have result code: CKD-CNHT- (quantity: 2)
Products with value 3 will have result code: CSN-CN- (quantity: 3)
Products with value 4 will have result code: CSN-CNHT- (quantity: 4)
Products with value 5 will have the result code: ĐT-IT-CN- (quantity: 5)
Products with value 6 will have the result code: ĐT-IT-CNHT- (quantity: 6)
Products with value 7 will have result code: LTTP- (quantity: 7)
Products with value 8 will have result code: DM- (quantity:8)
Products with value 9 will have result code: DG- (quantity: 9)
Code column after conversion
Each value in the Product column will be converted to a specific code, along with the corresponding quantity, as listed above.
Have a new virtual column with app formula:
SWITCH([Products],
1, “CKD-CN- (quantity: 1)”,
2, “CKD-CNHT- (quantity: 2)”,
.
.
.
9, “DG- (quantity: 9)”,
“Unknown”
)
That’s what I mean
My formula is:
IFS(
[Products] = 1, CONCATENATE(“CKĐ-CN-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 1))+1)),
[Products] = 2, CONCATENATE(“CKĐ-CNHT-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 2))+1)),
[Products] = 3, CONCATENATE(“CSN-CN-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 3))+1)),
[Products] = 4, CONCATENATE(“CSN-CNHT-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 4))+1)),
[Products] = 5, CONCATENATE(“ĐTCNTT-CN-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 5))+1)),
[Products] = 6, CONCATENATE(“ĐTCNTT-CNHT-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 6))+1)),
[Products] = 7, CONCATENATE(“LTTP-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 7))+1)),
[Products] = 8, CONCATENATE(“DM-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 8))+1)),
[Products] = 9, CONCATENATE(“DG-”, TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 9))+1)),
TRUE,“”
)
This is Result:
SWITCH([Products],
1, “CKD-CN-” &
COUNT(FILTER(“Test_Record”, [Products] = 1) - LIST([_ThisRow])) + 1,
2, “CKD-CNHT-” &
COUNT(FILTER(“Test_Record”, [Products] = 2) - LIST([_ThisRow])) + 1,
.
.
.
9, “DG-” &
COUNT(FILTER(“Test_Record”, [Products] = 9) - LIST([_ThisRow])) + 1,
“Unknown”
)
This formula is correct but I have used security filter according to display data of each user and it is affected by security filter. The formula will count according to each user record (Add By column). How to not be affected by security filter