Skip to Content
avatar image
Former Member

Suppress duplicate records prior to or across record group (multiple tables)

Situation: We are a hotel that is looking to do some analytics on our guests who have been flagged as arrived-not-checked-in (hereafter known as ANCI). We access 3 separate tables in our database to gather the necessary information: STAY (for arrival date and reservation information), IINFO (to identify the guest on the report - not used in the search) and TRANSLOG (to grab action of the guest marked ANCI).

Record select formula is as follows:
{STAY.S_ADATE} = {?arrival_date} and {TRANSLOG.L_LOGTYPE} = "RESCHG" and {TRANSLOG.L_LOGDESC} startswith "RESV-ANCI"

This will give us a list of all the stays that have the ANCI event in the transaction log.This list is bigger than just the number of stays, however, because "resv-anci" isn't used only in the event of them being initially marked, rather it appears like a status of the reservation. If front desk marks the ANCI flag, saves the reservation (creates a translog entry), then changes something else on the res - say, the number of guests - and saves it again, it creates another translog entry that starts with "resv-anci" since the reservation is still in anci status.

Visual example:
returned results from above record formula, record sorted by TimeANCI:

Suppressing duplicates (I used a grouping by StayID for this example) and splitting them into hour groups, the highlighted Sara shows up a second time in the 11am group.


Any way around this?

Database/Select Distinct Records doesn't work as every record is unique due to the varying time stamps.
Sorting by StayID and using "{STAY.S_STAYID}=previous({STAY.S_STAYID})" in the suppression formula in details doesn't carry over once you group it by TimeANCI as technically "KP2230AE" is the first record of it's like in the 10am grouping.
Making a group of StayID and hiding details works the same as above.

I've scoured the internet and have yet to find a solution. I suspect there might be something with the group formula? However, I don't know how to only select distinct StayIDs through the interface since you can't just type "select distinct({stay.s_stayid})".

Any insight would be appreciated! Thanks for your attention.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 23, 2018 at 04:37 AM

    Hi Joy,

    Please see if this works:

    1. Insert a Details b section

    2. Create a formula with this code and place on the Details b section:

    shared stringvar stayID;
    if INSTR(stayID, {table.stay_id}) = 0 then
    stayID = stayID + {table.stay_id} + ">";

    3. Suppress Details b section

    4. Go to the section expert > highlight the Details Section "a" > Click the formula button beside 'suppress' and use this code:

    shared stringvar stayID;
    INSTR(stayID, {table.stay_id}) > 0


    Add comment
    10|10000 characters needed characters exceeded