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
Announcements
The Google Cloud Community will be in read-only from July 16 - July 22 as we migrate to a new platform; refer to this community post for more details.
Log in to ask a question

Showing Error Need Help

Posted on 06-14-2023 10:45 PM
Sirfyaad
Silver 4
Silver 4
Reply posted on --/--/---- --:-- AM

i don't know! what is missing in this. but showing this error "Ops Something is Wrong, Please Scan Again" No entry is happening from my appsheet app. This error is showing again and again. how can fix this error. need help.

entry lockedentry locked

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

 

AND(
  OR(
    NOT(IN([_THIS], SCAN SHEET[AWB NO])),
    NOT(IN([_THIS], SHEET1[AWB NO])),
    NOT(IN([_THIS], SHEET2[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"),
      CONTAINS([_This], "SF1")
    )
  )
)

 

Error_Message_If_Invalid formula for column AWB NO (Text)
 

 

IFS(
  AND(
    IN([_THIS], 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]),

  AND(
    IN([_THIS], SHEET1[AWB NO]),
    [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " & 
    SELECT(SHEET1[DATE], [AWB NO] = [_THISROW].[AWB NO]),

  AND(
    IN([_THIS], SHEET2[AWB NO]),
    [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " & 
    SELECT(SHEET2[DATE], [AWB NO] = [_THISROW].[AWB NO]),

  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"),
      CONTAINS([_THIS], "SF1")
    )
  ),
  "Ops Something is Wrong, Please Scan Again"
)

 

 

0 10 368
Topic Labels
0 Likes
10 REPLIES 10

Posted on --/--/---- --:-- AM
Simon_Robinson
Silver 2
Silver 2
Reply posted on --/--/---- --:-- AM

I think its this bit in your error message

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"),
      CONTAINS([_THIS], "SF1")
    )
  ),
  "Ops Something is Wrong, Please Scan Again"

As your saying show this error message if it doesn't contain these items.  Ditto the valid if

0 Likes

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

@Simon_Robinson 

I removed the this expression but showing error.

0 Likes

Posted on --/--/---- --:-- AM
1minManager
Gold 4
Gold 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

The generic error message?

0 Likes

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

Tell me how to fix this error.

0 Likes

Posted on --/--/---- --:-- AM
1minManager
Gold 4
Gold 4
In response to Simon_Robinson
Reply posted on --/--/---- --:-- AM

Remove the NOT() from this bit in both the valid if and error message formulas

0 Likes

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

Hi @1minManager @Simon_Robinson I have removed the NOT() function but still the error is showing.

AND(

AND(

IN([_THIS], SCAN SHEET[AWB NO]) = FALSE,

IN([_THIS], SHEET1[AWB NO]) = FALSE,

IN([_THIS], SHEET2[AWB NO]) = FALSE

),

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

LEN([AWB NO]) > 6,

AND(

CONTAINS([_This], "CRP") = FALSE,

CONTAINS([_This], "~") = FALSE,

CONTAINS([_This], "&") = FALSE,

CONTAINS([_This], "") = FALSE,

CONTAINS([_This], "MYNR0") = FALSE,

CONTAINS([_This], "/") = FALSE,

CONTAINS([_This], "CAMPU") = FALSE,

CONTAINS([_This], "SB") = FALSE,

CONTAINS([_This], "DLVP") = FALSE,

CONTAINS([_This], "RVPMF") = FALSE,

CONTAINS([_This], "BS0") = FALSE,

CONTAINS([_This], "SF1") = FALSE

)

)

 

Error message

 

IF(

  AND(

    IN([_THIS], SCAN SHEET[AWB NO]),

    [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]

  ),

  CONCATENATE(

    "Already Scanned On ",

    SELECT(

      CAMPUS SCAN SHEET[DATE],

      [AWB NO] = [_THISROW].[AWB NO]

    )

  ),

  IF(

    AND(

      IN([_THIS], SHEET1[AWB NO]),

      [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]

    ),

    CONCATENATE(

      "Already Scanned On ",

      SELECT(

        SHEET1[DATE],

        [AWB NO] = [_THISROW].[AWB NO]

      )

    ),

    IF(

      AND(

        IN([_THIS], SHEET2[AWB NO]),

        [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]

      ),

      CONCATENATE(

        "Already Scanned On ",

        SELECT(

          SHEET2[DATE],

          [AWB NO] = [_THISROW].[AWB NO]

        )

      ),

      IF(

        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"),

          CONTAINS([_THIS], "SF1")

        ),

        "Ops Something is Wrong, Please Scan Again",

        ""

      )

    )

  )

)

 

0 Likes

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

I have been trying to fix it for many days but no one is bothering me properly.

0 Likes

Posted on --/--/---- --:-- AM
1minManager
Gold 4
Gold 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

Its hard to be more helpful without fully understanding what all these columns & tables do in your app.  When I'm in your current situation (which is often ๐Ÿ˜‚) the only way to work it out is to remove the Error Message and strip the VALID IF formula back to one item e.g.

OR(
NOT(IN([_THIS], SCAN SHEET[AWB NO])),
NOT(IN([_THIS], SHEET1[AWB NO])),
NOT(IN([_THIS], SHEET2[AWB NO]))
)

Then check if it triggers on this.  If not try the next item

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

and so on until you find the indiviual item that is wrong.  Once you have that you should be able to fix it or come back here for advice.  

0 Likes

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

Hi @1minManager @Simon_Robinson Thank for Response. 

[AWB NO] my scan column. I want that if someone scans a duplicate AWB No, then along with the duplicate error message, the date should also appear inside the error.

Valid if 

AND(
  OR(
    NOT(IN([_THIS], SCAN SHEET[AWB NO])),
    NOT(IN([_THIS], SHEET1[AWB NO])),
    NOT(IN([_THIS], SHEET2[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"),
      CONTAINS([_This], "SF1")
    )
  )
)

For which I have given the expression of valid if and error message. But whenever I make an entry in the [AWB NO], error appears whether it is duplicate or Not.

Error Messsages

IFS(
  AND(
    IN([_THIS], 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]),

  AND(
    IN([_THIS], SHEET1[AWB NO]),
    [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " & 
    SELECT(SHEET1[DATE], [AWB NO] = [_THISROW].[AWB NO]),

  AND(
    IN([_THIS], SHEET2[AWB NO]),
    [_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
  ),
  "Already Scanned On " & 
    SELECT(SHEET2[DATE], [AWB NO] = [_THISROW].[AWB NO]),

  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"),
      CONTAINS([_THIS], "SF1")
    )
  ),
  "Ops Something is Wrong, Please Scan Again"
)

And I want that if any of these values โ€‹โ€‹match then this error should be shown "Ops Something is Wrong, Please Scan Again". I want to show error message if there is duplicate, and if any of these values โ€‹โ€‹match then show error message.

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"),
      CONTAINS([_THIS], "SF1")
    )
  )

 If possible, please current my expression so that it becomes easier for me.

0 Likes

Posted on --/--/---- --:-- AM
1minManager
Gold 4
Gold 4
In response to Sirfyaad
Reply posted on --/--/---- --:-- AM

Does the valid if work as expected?

 

0 Likes
Top Labels in this Space