This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

Expression Error

Posted on 11-20-2022 02:12 AM
Sirfyaad
Silver 4
Silver 4
Reply posted on --/--/---- --:-- AM

I have duplicated from two sheets, here the expression is working. But the duplicate remark and edit not details is not showing. Please fix my expression.

Valid If formula for column AWB NO (Yes/No,List)

 

 

AND(
    OR(
       NOT(IN([_THIS], MPOP SCAN SHEET[AWB NO])),
       [_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
    ),

OR(NOT(IN([_THIS], SHEET1[AWB NO])),
       [_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
    ),

    LEN([AWB NO]) > 6,

    NOT(
        OR(
           CONTAINS([_This], "CRP"),
           CONTAINS([_This], "~"),
           CONTAINS([_This], "&"),
           CONTAINS([_This], "_"),
           CONTAINS([_This], "MYNR0"),
           CONTAINS([_This], "/"),
          CONTAINS([_This], "CAMPU")
 )))

ALL these statements are true:
....1: ANY of these statements is true:
........1: This statement is false:
............(The value of column 'AWB NO') is one of the values in the list (MPOP SCAN SHEET[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....2: ANY of these statements is true:
........1: This statement is false:
............(The value of column 'AWB NO') is one of the values in the list (Sheet1[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....3: (The text length of (The value of column 'AWB NO')) is greater than (6)
....4: This statement is false:
........ANY of these statements is true:
............1: (The value of column 'AWB NO') contains the text value ("CRP")
............2: (The value of column 'AWB NO') contains the text value ("~")
............3: (The value of column 'AWB NO') contains the text value ("&")
............4: (The value of column 'AWB NO') contains the text value ("_")
............5: (The value of column 'AWB NO') contains the text value ("MYNR0")
............6: (The value of column 'AWB NO') contains the text value ("/")
............7: (The value of column 'AWB NO') contains the text value ("CAMPU")

 

 

 
Error_Message_If_Invalid formula for column AWB NO (Text)
 

 

 

IFS(
  AND(
       IN([_THIS], MPOP SCAN SHEET[AWB NO]),
       [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  )
  "Already Scanned This Shipment On " &
       SELECT(MPOP SCAN SHEET[Date], [AWB NO] = [_THISROW].[AWB NO],
 LEN([AWB NO]) > 6),AND(

IN([_THIS], SHEET1[AWB NO]),
       [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO])

"Already Scanned This Shipment On " &
       SELECT(SHEET1[Date], [AWB NO] = [_THISROW].[AWB NO],
 LEN([AWB NO]) > 6),

  NOT(
      OR(
         CONTAINS([_This], "CRP"),
         CONTAINS([_This], "~"),
         CONTAINS([_This], "&"),
         CONTAINS([_This], "MYNR0"),
         CONTAINS([_This], "_r1"),
         CONTAINS([_This], "CAMPU")
      )
  ),
  "Invalid Entry, Rescan AWB NO"
)

IFS(
....ALL these statements are true:
........1: (The value of column 'AWB NO') is one of the values in the list (MPOP SCAN SHEET[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is not equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....ALL these statements are true:
........1: (The value of column 'AWB NO') is one of the values in the list (Sheet1[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is not equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....This statement is false:
........ANY of these statements is true:
............1: (The value of column 'AWB NO') contains the text value ("CRP")
............2: (The value of column 'AWB NO') contains the text value ("~")
............3: (The value of column 'AWB NO') contains the text value ("&")
............4: (The value of column 'AWB NO') contains the text value ("MYNR0")
............5: (The value of column 'AWB NO') contains the text value ("_r1")
............6: (The value of column 'AWB NO') contains the text value ("CAMPU")
...."Invalid Entry, Rescan AWB NO")

 

 

 

 

0 15 177
Topic Labels
0 Likes
15 REPLIES 15

Posted on --/--/---- --:-- AM
WillowMobileSys
Platinum 1
Platinum 1
Reply posted on --/--/---- --:-- AM
@Sirfyaad wrote:

But the duplicate remark and edit not details is not showing.

What does this mean?  We need more explanation of the issue and what you are expecting to happen.

 

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to WillowMobileSys
Reply posted on --/--/---- --:-- AM

Hi @WillowMobileSys I have two sheets, one in new sheet and one in old sheet. I want to add duplicate expression from both the sheets. So that when I scan, I can know in which sheet the entry has been made here

0 Likes

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

@WillowMobileSys  Hi 

0 Likes

Posted on --/--/---- --:-- AM
WillowMobileSys
Platinum 1
Platinum 1
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

Can you show images of the use case that is not working?  I don't understand your explanation and how it related to the validation expression.

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to WillowMobileSys
Reply posted on --/--/---- --:-- AM

Hi @WillowMobileSys 

I have duplicated from current sheet, and I want to duplicate from another sheet, second sheet name is Scan sheet 2.

Curren Using 

Valid If formula for column AWB NO (Yes/No,List)

 

AND(
    OR(
       NOT(IN([_THIS], CAMPUS SCAN SHEET[AWB NO])),
       [_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
    ),

    LEN([AWB NO]) > 6,

    NOT(
        OR(
           CONTAINS([_This], "CRP"),
           CONTAINS([_This], "~"),
           CONTAINS([_This], "&"),
           CONTAINS([_This], "_"),
           CONTAINS([_This], "MYNR0"),
           CONTAINS([_This], "/"),
           CONTAINS([_This], "CAMPU"),
           CONTAINS([_This], "SB"),
           CONTAINS([_This], "DLVP"),
           CONTAINS([_This], "RVPMF"),
           CONTAINS([_This], "BS0")
 )))

 

 

Error_Message_If_Invalid formula for column AWB NO (Text)

 

IFS(
  AND(
       IN([_THIS], CAMPUS SCAN SHEET[AWB NO]),
       [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " &
       SELECT(CAMPUS SCAN SHEET[DATE], [AWB NO] = [_THISROW].[AWB NO],
 LEN([AWB NO]) > 6),

  NOT(
      OR(
         CONTAINS([_This], "CRP"),
           CONTAINS([_This], "~"),
           CONTAINS([_This], "&"),
           CONTAINS([_This], "_"),
           CONTAINS([_This], "MYNR0"),
           CONTAINS([_This], "/"),
           CONTAINS([_This], "CAMPU"),
           CONTAINS([_This], "SB"),
           CONTAINS([_This], "DLVP"),
           CONTAINS([_This], "RVPMF"),
           CONTAINS([_This], "BS0"))
  ),
  "Ops Something is Wrong, Please Scan Again"
)

 

 

0 Likes

Posted on --/--/---- --:-- AM
WillowMobileSys
Platinum 1
Platinum 1
Reply posted on --/--/---- --:-- AM
@Sirfyaad wrote:

I have duplicated from current sheet, and I want to duplicate from another sheet, second sheet name is Scan sheet 2.

Oh!  You want to modify the expression to check for duplicates from two different sheets/tables.

I recommend NOT having two sheets.  Combine them into a single sheet.  It will only cause additional implementation trouble for you going forward.  You can segregate them within the app by using Slices if you need that.

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
Reply posted on --/--/---- --:-- AM
@WillowMobileSys wrote:

two

I want to duplicate current sheet to second sheet. 

 

0 Likes

Posted on --/--/---- --:-- AM
WillowMobileSys
Platinum 1
Platinum 1
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM
@Sirfyaad wrote:

want to duplicate current sheet to second sheet. 

Then I don't understand.  If you are simply duplication rows from "Campus Scan Sheet" to "Scan Sheet 2", BOTH will have the same rows and there is no need to validate against them both.

However, If you insist for some unexplained reason..

You can modify the expression as follows to check for duplicates from two different sources:

 

AND(
    OR(
       AND(
           NOT(IN([_THIS], CAMPUS SCAN SHEET[AWB NO])),
           NOT(IN([_THIS], SCAN SHEET 2[AWB NO])),
       ),
   
       [_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
    ),

    LEN([AWB NO]) > 6,

    NOT(
        OR(
           CONTAINS([_This], "CRP"),
           CONTAINS([_This], "~"),
           CONTAINS([_This], "&"),
           CONTAINS([_This], "_"),
           CONTAINS([_This], "MYNR0"),
           CONTAINS([_This], "/"),
           CONTAINS([_This], "CAMPU"),
           CONTAINS([_This], "SB"),
           CONTAINS([_This], "DLVP"),
           CONTAINS([_This], "RVPMF"),
           CONTAINS([_This], "BS0")
 )))

 

I the expression below, I corrected a couple errors and remove unnecessary portions

 

IFS(
  AND(
       OR(
          IN([_THIS], CAMPUS SCAN SHEET[AWB NO]),
          IN([_THIS], SCAN SHEET 2[AWB NO]),
       )
       [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " &
       IF(ISNOTBLANK(SELECT(CAMPUS SCAN SHEET[DATE], [AWB NO] = [_THISROW].[AWB NO])),
          ANY(SELECT(CAMPUS SCAN SHEET[DATE], [AWB NO] = [_THISROW].[AWB NO]),
          ANY(SELECT(SCAN SHEET 2[DATE], [AWB NO] = [_THISROW].[AWB NO])
       ),

  TRUE, "Ops Something is Wrong, Please Scan Again"
)

I hope this helps!

 

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to WillowMobileSys
Reply posted on --/--/---- --:-- AM

Not Working Showing Error

Expression 'AND( OR( AND( NOT(IN([_THIS], MPOP CAMPUS SCAN SHEET[AWB NO])), NOT(IN([_THIS], SHEET1[AWB NO])), ), [_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO] ), LEN([AWB NO]) > 6, NOT( OR( CONTAINS([_This], "CRP"), CONTAINS([_This], "~"), CONTAINS([_This], "&"), CONTAINS([_This], "_"), CONTAINS([_This], "MYNR0"), CONTAINS([_This], "/"), CONTAINS([_Th...' was unable to be parsed: Sequence contains no elements.
0 Likes

Posted on --/--/---- --:-- AM
WillowMobileSys
Platinum 1
Platinum 1
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

There was a recent discovery of a bug that has crept in>

Try replacing this:

[_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]

with this:

[_THISROW_BEFORE].[AWB NO] = [AWB NO]

 Do you still get the same error?

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to WillowMobileSys
Reply posted on --/--/---- --:-- AM

Still showing the same error.

0 Likes

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

Hi @WillowMobileSys 

You have tried many times to help me but till now I have not been able to fix it. I want to duplicate from two sheets. First from the current sheet and second from the scanned sheet. Current sheet is my main sheet. 

 

0 Likes

Posted on --/--/---- --:-- AM
Steve
Platinum 4
Platinum 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

Sequence contains no elements often means you have a dangling comma.

0 Likes

Posted on --/--/---- --:-- AM
Sirfyaad
Silver 4
Silver 4
In response to Steve
Reply posted on --/--/---- --:-- AM

Didn't understand what you want to say. 

0 Likes

Posted on --/--/---- --:-- AM
JuneCorpuz
Gold 1
Gold 1
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM 
AND(
	[_THIS] = TRUE,
	[_THIS] = FALSE,
)

It means that you have a comma before you close an expression.

As you can see in the example above, there's "," after the word FALSE and before the closing parenthesis which results to Sequence contains no elements error

Top Labels in this Space