>

Identify missing events sequence

Answered
Anitha Khairnar
Edited
  • Description of the issue: Events seems missing as per the sequence. Not available in ‘Events’ window nor in Cancelled and Archived Events window
  • Account name: Kallang Alive Sport Management Co Pte. Ltd.
  • Region: APAC
  • Client version: 24.4
  • Steps to reproduce:
    • Main menu > Events
    • We have noted that some events sequence is missing
    • Some events are not available in ‘Events’ window or in Cancelled and Archived Events
    • Please let us know the possibility to find the missing events sequence
  • Observed results vs Expected results:
  • What is the job the client is trying to accomplish:
    • They are trying to trace all the events in sequence
  • Business impact if not resolved: Normal
  • Links to articles where you've looked for solutions and also other steps you've taken to research the issue:
  • Link to Zendesk ticket: https://ungerboeck.zendesk.com/agent/tickets/357720
  • Local v30 URL: In progress

Comments

12 comments

Date Votes
  • Official comment
    Ryan Ungerboeck

    In the end - for most tables (pretty much anything other than invoicing) we do not provide audit level details as to why a number was skipped.

    I cannot speak on events for the most common reasons that this may occur - but I imagine its due to failing some sort of validation on a child table (or OSB or online booking related as discussed above).  So while we won't spend the event ID if it fails a basic event level validation - if you try to enter an event with an invalid/incomplete booking record - you'll find a pretty easy scenario where an event ID will get skipped - but its one among many possibilities.

    Below will give you a partial list of event IDs that were skipped (wouldn't account for if 2 event IDs in a row were skipped) and the approximate date that they were skipped based on the entered stamp of the next event entered.  

    SELECT EV200_EVENT_MASTER.EV200_ORG_CODE, EV200_EVENT_MASTER.EV200_EVT_ID - 1 AS MISSING_EVENT_ID, EV200_EVENT_MASTER.EV200_ENT_STAMP AS APPROXIMATE_TIME FROM EV200_EVENT_MASTER
    LEFT OUTER JOIN EV200_EVENT_MASTER PREV_EVENTS ON EV200_EVENT_MASTER.EV200_ORG_CODE = PREV_EVENTS.EV200_ORG_CODE AND EV200_EVENT_MASTER.EV200_EVT_ID = PREV_EVENTS.EV200_EVT_ID + 1
    WHERE PREV_EVENTS.EV200_ORG_CODE IS NULL 
    ORDER BY 1, 2 DESC

  • Seth Halvaksz

    Mike Schepker please review

  • Mike Schepker

    Could this be from an abandoned event in OSB? I know back in the iEBMS days we did something similar with order sequences. If an order was abandoned in iEBMS SOP, that order number would no longer be usable and so it moves on to the next order number. Maybe OSB does something similar with Events? That is the only thing I can think of.

  • Anitha Khairnar

    Thanks Mike Schepker . These all events missing does not seem to be OSB specific. What is the general practice we look for an missing event sequence? How can this be conveyed to the customer regarding the missing events sequence.

    Also in what scenario do the sequence goes out of sync? Please advice.

  • Mike Schepker
    Edited

    Anitha Khairnar I don't know the answer to any of your questions. 

  • Anitha Khairnar

    Hello Seth Halvaksz and Ryan Ungerboeck 

    Can you please advise if we can have an SQL query to run to know about the missing events sequence.

  • Seth Halvaksz

    Anitha Khairnar please talk to Sara Noonan for generating a list of deleted events

  • Sara Noonan

    Anitha Khairnar you can *try* the following - might require a little Excel magic.

    I also don't see anything in data model about event sequence - do you mean event ID? Assuming that's what you mean: 

    Run this SQL statement in the local copy of the database - this SQL statement will pull all events in order of event ID.

    SELECT EV200_EVT_ID, EV200_ORG_CODE, EV200_EVT_DESC, EV200_EVT_STATUS
    FROM EV200_EVT_MASTER
    WHERE EV200_ORG_CODE = ‘ORG CODE’ 
    ORDER BY EV200_EVT_ID

    Right click > select all
    Right click > copy with headers
    Paste into Excel spreadsheet
    Note: if there are leading zeroes on the event IDs, you'll need to ensure those get copied into Excel (that usually doesn't happen but just in case)
    Make sure that the event IDs are in column A in your spreadsheet
    Paste this formula in row 2 of a column without data (will probably be column E, so cell E2): =IF(ROW()=2, "", IF(A2=A1+1, "", "❗Break"))
    Hover over the lower right corner of cell E2 (you should see a +) and drag down to the bottom of the spreadsheet
    Non-sequential values should be flagged with “! Break” - you can sort on that column to get a full list, or leave it as is so the customer can view the data in context. 

    Let me know if you run into trouble or can't get the formula to work - feel free to just send me the Excel doc.
     

  • Chanh Huynh

    Hi team. 

    The customer has escalated this saying it is an audit finding and concern that we allow for event IDs to be skipped. Reading through the posts above, would it be possible for our Cancelled and Archived Events area to include event IDs that were reserved during and event creation process but then failed validation to ensure complete sequential of event IDs? The auditors are wanting to either find the event IDs in the Events page or the Cancelled and Archived Events page. 

    Let me know if this is plausible and I will pop this into Pendo.

  • Seth Halvaksz

    Chanh, sure you can open an enhancement for this and add the link here.

    For now, the best way to identify these skipped events is the SQL that Sara provided.

  • Chanh Huynh

    Anitha Khairnar - can you please run the SQL Sara provided and see what this yields? I will pop in an enhancement into Pendo. Thank you

  • Anitha Khairnar

    Hello Chanh Huynh I have both the queries executed and have attached the sheet in the ticket.

Please sign in to leave a comment.

Didn't find what you were looking for?

New post