Remove duplicate names in Micros GSS

[87Hog]

I am trying to use the version 3.2 Guest Services Solutions module for Micros 3700. Last year our reseller imported a csv file of our dining club members four times. I am trying to export names and getting duplicates.

Can I connect to Micros.db using Sybase, open the table where the customer data resides, and delete duplicate records?

Is it possible to extract the table, make modifications, and then place it back in the database?

Thanks in advance for any solutions

 

[Shift4SMS]

You are going to need to know a little more about the database schema (structure). Simply deleting duplicate records my break associated links to those records from other tables that are depending on this data.

Once you find the schema, you may find that there are not other dependent tables, in which case deleting the duplicates would not have side effect. If there are dependent tables, you may need to relink the records to the single non-duplicate you will be leaving behind.

My guess is that MICROS either already has a utility to accomplish what you are looking for or there is a premade SQL script available somewhere that will do it. Ask your rep and/or try a Google search.

Hope this helps.

Steve Sommers
Shift4 Corporation --

http://www.shift4.com

Creators of $$$ ON THE NET(tm) payment processing services

 

[pmegan]

The customer definition table is micros.gss_customer_status. You can see which tables have foreign key links to it in Sybase Central. Log in, expand gss_customer_def in the tables section and click the Referenced By folder.

Make sure the customers are actually duplicated. If you're joining table together in your extraction query, what looks like duplicate customers may be multiple records somewhere in another able referencing the same customer.

If you have real duplicate guest records, whether you can delete them is going to depend on whether the guest accounts have an order history or not. GSS tables in Res32 are owned by the Micros user. SQL definition users, (support, custom, installer), are allowed to edit definition tables (ending in 'def'), but not detail or totals tables (ending in 'dtl').

This query should help find the duplicates and determine which ones have an order history. Uncomment the where clause to only include active guests.

select c.contact_last_name, c.customer_seq, c.contact_first_name, c.active,count(o.order_ttl_seq)
from micros.gss_customer_def c left outer join micros.gss_order_ttl o
// where c.active = 'T'
group by c.customer_seq, c.contact_first_name, c.contact_last_name, c.active
order by c.contact_last_name, c.contact_first_name, c.active

I would say to start off by marking any duplicates as inactive instead of trying to delete them. That should take them out of circulation for order taking.