using mi_seq vs obj_num for tracking items 1

[microsNewb08]

Hi, I am new to Micros 3700. My client's data extraction queries and reports are using obj_num to track items. Since their menu items are continuously changing (unused ones are taken off the menu and new ones are added), they want to start reusing obj_numbers (obj_num) for new menu items.

This implies that all stored procs and references need to be changed to use mi_seq instead of obj_num for items tracking.
The reason why they want to start recycling the [obj_num]bers is to keep the ranges consistent for new menu items as they are added.

Has someone run into the same question? Is this a reasonable approach? What are the pros/cons of using mi_seq vs. obj_num for transactional level data loading?

I would greatly appreciate everyone's feedback.

 

[pmegan]

I never track anything by obj_num; menu items, employees, reporting group, etc... are all exported with their respective sequence number. The object numbers are just integer fields and it's too easy for the configurator users to change them. Plus, the sequence field is the primary key for all the tables, so your queries will be more efficient.

Switching an existing system from obj_num to mi_seq tracking depends on a few things:
What you're doing with the data
How is the number, mi_seq or obj_num, being used/referenced
What your client means by recycling object numbers.

If they're just going to write over retired menu items, changing the tracking probably won't help anything. you'll still have multiple items using the same record. Also, if they use the Micros menu item reports at all they'll be a mess.

Personally, whenever I set up a menu I give large ranges for the menu groups. For example, Appetizers start at obj_num 1000 and entrees start at obj_num 2000. I'll only put in records for obj_nums 1000-1100, but if they pass the 100 app mark they can still add up to 900 more before the numbers run out. (The only time this has ever been a problem for me was with the old Remacs inventory which required the mi_seq and obj_num to match, and choked on gaps in the number sequence.) If your client is open to the idea, and it wouldn't cause too much reprogramming misery, it might be worth the hour or two it would take.

You may also want to look into using the Menu Item groups. Hardly anybody uses them for reporting and they're a great way to group items for exports.

 

[microsNewb08]

Thanks a bunch for the feedback!

The data are loaded into a data warehouse with subsequent reporting on daily sales, etc. The main data pulling queries are using obj_num to identify items when menu_item_groups are not applicable (there are a bunch of them with '-1' assigned to family and major groups - i.e. non-standard items?).

The recycling of obj_nums under consideration fits the scenario you described (menu groups are set in ranges with obj_num range for appetizers, entrees, etc.). However, the ranges are narrower and the current object numbers used are pushing the range borders.

I see the benefit of rewriting the queries to use mi_seq vs. obj_num. However, I didn't quite understand your comment re: 'multiple items still using the same record'. If the obj_nums are reused, wouldn'they be new menu items (based on unique mi_seq identifiers generated each time we create a new menu item)? If so, is there a way to prevent the mess when dealing with micros menu item reports?

Thanks again for the post.

Best regards,

 

[pmegan]

The 3700 holds onto historical totals for a set amount of time. There's a true relation between the menu item definition and historical totals tables so you can't delete an item that's been retired until all sales data for that item has been purged. This is why recycling the numbers gets tricky.

In most cases the restaurant just writes over the old item definition to re-use an object number, which will cause the report mess i was talking about and isn't the way to go.

My suggestion would be to create a section at the bottom of the menu item file for retired items. So when an item is no longer available add something like 900,000 to the object number. This will push it out of the way and allow you to add in a new record with the object number you want to recycle.

If you're pushing this into a warehouse I would definately go with the mi_seq tracking. Even if your client doesn't want it now, at some point they're going to want long term consolidated reports on menu item sales. If they're tracked and grouped by obj_num you'll end up with sales for all items that used a particular obj_num during the report period summed up together. Using the sequence will let you group by mi_seq and sort by obj_num. This will produce multiple line items with the same obj_num, showing what items where sold under each object number.


Is the -1 assigned to the family/major groups the group name? I'm pretty sure the tables that hold those definitions require a positive integer for both the seq and obj numbers.

 

[microsNewb08]

Thanks again for the info. Sorry, I have been away from the forum for a few days.

Do you suggest that the historical data be rewritten (updated) to include mi_seq in addition to obj_num, so that when obj_numbers are reused the references to historical item sales will remain unique? Otherwise, if I switch to mi_seq tracking the old records will become orphaned (with no unique references to the menue items). Have you ever done this? If so what are the pitfalls to watch for when emarking on such a data restructuring project?

Yes, the seq and obj numbers are all positive. The -1 values are the group identifiers stored in custom tables tracking major and family groups.

 

[pmegan]

This is my first time back here after my last post so i didn't even notice the delay. Things are too busy for me to get here more than once or twice a week.

If it were me I would update the historical data. I did this for a custom program that pulls data from our POS and inventory databases and reports on items that were sold but have no definition in the inventory system.

Basically just add an mi_seq int or bigint field to the table(s) that hold menu item data and set it to allow nulls. Run an update to fill it in for all the rows, then alter the table to disallow nulls. Also, if your primary key is on the obj_num column you'll have to change it. I have the mi_seq column as the pimary key and a non-unique index on the obj_num column.

Just make sure the joins in queries, store procedures, reports, etc... are changed from obj_num to mi_seq. Also, if your application is written in one of the .Net languages, make sure you update the structure, keys and relationships in any strongly-typed datasets. Those are the only potential problems I can think of and all can be avoided if you're careful.

 

[microsNewb08]

Sorry, it took me a while to get back to the forum. Thank you again for the valuable advice!