Skip to Content
0

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

Feb 23 at 04:16 AM

11

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Feb 23 at 04:37 AM
0

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

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded