Micros 3700 Temporary price increase

[NorNev]

I have a city wide event coming up and would like to raise my liquor/beer prices for this event and then return to the original pricing structure after the 3 day event is over. Is there a way to do this through the Family Groups and if so how?

 

[Moregelen]

You could do it with menu levels. Or you could also go into the database and run a single sql statement to increase everything by a % and then reverse it after the event.

 

[pmegan]

Do you know if you're using multiple prices on your menu items? If you only enter Price 1 for new items you're most likely not, if you enter prices in all or a few of the price slots you probably are.

 

[NorNev]

Thanx Moregelen and pmegan for both of your responses.
We do not use multiple pricing on our menu items and the price for each item is slotted into Price 1. We do not run any Happy Hour pricing either.
I am somewhat familiar with the menu level configurator but am stuck on figuring out how to have a secondary price (Price 2?) activated for the 72 hour period we want to run the higher price.
I know how to do a lot of the common maintenance projects on Micros (adding items, SLUs, Condiments, etc) but have picked this project up as our Micros IT person is out for a while. Any specific direction would be greatly appreciated.
Thanx again for your help.

 

[pmegan]

Ok, there are 2 ways to go about this.

[ol 1]
[li]If the price changes are a standard amount or percentage across the boards it would be easier to run an SQL query that stores the current priced in an unused slot and updates price 1. When the event is over you would just run another query that copies the prices back into price 1.[/li]

[li]If the price change is not uniform it would probably be best to do this with levels, which is going to be a bit more complex, but you'll be able to do enter the prices and test things ahead of time, and then "flip a switch" to make them active when the time comes. Going this route you'll have to determine how your levels and autolevels work.[/li]
[/ol]

Either way, there's going to be some SQL work going on, if your comfortable, or even familiar, with SQL it'll be a big help. You'll also have to determine which menu item classes the affected items will be using.

 

[Moregelen]

If you are looking to modify every item by a flat percentage, the easiest and quickest route is through an SQL statement that you would run. You could even setup a windows task event to run a batch file the day of to raise the prices, and then one to run a different batch file afterwards that would set them all back down again. You can even filter which prices you modify.

To give an example:

So say I want to modify the prices of all of my Hot Dogs (which I know is major group seq 110), bottled drinks (which I know is major group seq 120) by a 10% increase I could just run this sql statement:

UPDATE micros.mi_price_def 
SET preset_amt_1 = preset_amt_1 * 1.10
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE fam_grp_seq = 110 OR
    fam_grp_seq = 120
)

And then afterwards, I could just run it again but in reverse:

UPDATE micros.mi_price_def 
SET preset_amt_1 = preset_amt_1 / 1.10
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE fam_grp_seq = 110 OR
    fam_grp_seq = 120
)

Or say I just wanted all of my Food, which is Major Group Seq 1 in my database, updated by 10%

UPDATE micros.mi_price_def 
SET preset_amt_1 = preset_amt_1 * 1.10
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE maj_grp_seq = 1
)

Same process as the first option to reverse it

UPDATE micros.mi_price_def 
SET preset_amt_1 = preset_amt_1 / 1.10
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE maj_grp_seq = 1
)

Of course, if you just want to hit every single item in your database, you can run it without the where clause.


Personally, this is how I would do it given just how difficult it would be to modify all of the Menu Item Classes and Menu Items to enable a second level or pricing, not even considering setting up the system to automatically bump me into that level for those three days.. and then undoing it all! This way, I only have to run an SQL the day of, and then run a different SQL when done.

Still, I understand that some people are leery of directly editing the database.

 

[Moregelen]

And FYI, you can make those changes with the stock custom,custom user.

 

[Moregelen]

Although, thinking on it, I agree with pmegan. Should probably store the original price in one of the unused slots, just in case there are rounding errors.

An example on the modification:

UPDATE micros.mi_price_def 
SET preset_amt_2 = preset_amt_1,
preset_amt_1 = preset_amt_1 * 1.10
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE maj_grp_seq = 1
)

And then to undo it:

UPDATE micros.mi_price_def 
SET preset_amt_1 = preset_amt_2,
preset_amt_2 = null
WHERE mi_seq IN (
    SELECT mi_seq FROM micros.mi_def
    WHERE maj_grp_seq = 1
)

 

[pmegan]

Yep, definitely the easiest way to do it if there's a uniform price change. The last time I had to do this there were different price changes for domestic/imported beer, standard/top shelf liquor and glass/bottle wine. It was easier to go with levels in that case since I was able to spend a couple of days adjusting and ringing in test orders using a change level button. When the time came I just changed the level in the autolevels from 5 to 6, and back to 5 when the promotion was done.

 

[Moregelen]

Man, that's rough. I had a customer about a month back who ran a different promotional every..single..day. They also wanted each menu item separate rather than just changing the price level, because the reporting wouldn't differentiate. They drove me nuts, especially when they starting trying to modify things themselves. *head desk*

Auto levels are a pain to avoid if you can. If it isn't a uniform price change though, set aside a few days.. you're going to need 'em.

 

[NorNev]

Thanx to both of you for all of your assistance!
Have to go with levels as there are different price changes for the various products.
I will definitely keep this thread as I know the sql statements that were provided will come in handy in future endeavors.

I sincerely appreciate the time and assistance provided. You all are great.

 

[pmegan]

Ok, there's going to be a bit of work to get that done.

Here's what you'll have to check:

Autolevels: Do you know if you have them running? In the POS configurator go to the Sales tab / Menu Levels button / Auto Menu Levels tab. If you have entries there you're using autolevels. Write down the RVC, Period, Main and Sub level for each entry. This will make a difference in how you switch over to the temporary prices.

Menu Item Classes: take a look at the menu item classes used by your liquor items. Sales tab / Menu Item Classes button. There are 2 options on the Price/Totals tab we're interested in - Enable prices 2 through 10, and Use Sub Menu Level For Prices. Write these down for each of your liquor classes. While you're at it, take a look at the rest of your classes and mark down any that have either of these options checked.

Once we know what we're dealing with as far as your levels go we'll be able to decide what price slot can be used for the temporary pricing.

 

[NorNev]

Autolevels: Do you know if you have them running? In the POS configurator go to the Sales tab / Menu Levels button / Auto Menu Levels tab. If you have entries there you're using autolevels.
No Entries in the Auto Menu Levels Tab

Menu Item Classes: take a look at the menu item classes used by your liquor items. Sales tab / Menu Item Classes button. There are 2 options on the Price/Totals tab we're interested in - Enable prices 2 through 10, and Use Sub Menu Level For Prices. Write these down for each of your liquor classes.
Done. Below is the standard Menu Item Classes set-up for like classes:
LIQ ITEM
LIQ ITEM 2-NM
LIQ ITEM W/LVL
Only the "W/LVL" class has the "enable prices 2-10".
None of the classes have "Use sub menu level for prices" checked.

 

[pmegan]

That LIQ ITEM W/LVL may be just be a default class from your dealer, but here's how to see what it's doing. In the Menu Item setup screen click on your search button. There will be two drop down boxes on the bottom right of the screen. Select Menu Item Class in the first one and LIQ ITEM W/LVL in the second. This will show you any items using that class. If you have any, take a look at the prices and make sure they're only using price 1. If there are any other prices filled in don't go forward here.

It seems like the easiest think to do is copy your prices into unused slots and make the changes you need to those prices. When the event starts you'll run a script that copies the temp prices to the active slot, and one that restores the original prices when it's done.

First, let's check a couple of price slots so we don't accidentally overwrite anything important. Run this query through SQL:

select count(*) from micros.mi_price_def
where 
    (preset_amt_5 is not null and preset_amt_5 <> 0) or
    (preset_amt_6 is not null and preset_amt_6 <> 0)

If you come up with 0 we're good to go. If you get a number back that means there's a menu item somewhere using price 5 or 6.

Again, if you don't get a 0 back don't continue.

This query will copy price 1 into both price 5 and 6. We'll use price 5 as the temporary prices and price 6 as the current prices that will be rolled back after the event. Change [red]x, y, z[/red] to the menu item class numbers for your three liquor classes from the configurator.

update micros.mi_price_def
set 
    preset_amt_5 = preset_amt_1
    , preset_amt_6 = preset_amt_1
where mi_seq in (
    select m.mi_seq from
        micros.mi_def m 
        join micros.mi_type_class_def c
            on m.mi_type_seq = c.mi_type_seq
    where c.obj_num in ([red]x, y, z[/red])
);
commit;

Take a look at your liquor items. They should all have entries for prices 5 and 6. Go ahead and start changing price 5 to the prices you're going to use during the event. When you're done we'll get the script going to make them active.

 

[NorNev]

Ran the search in the Menu Item screen.
The only result that came up is:
WELL RUM
Price 1 2.75
Price 2 3.25
Price 3 3.50
I don't know why this is set up this way, all other menu items only have one price.
The WELL RUM item is using Price 2.

I ran the first query for price slots 5 & 6.
Came back 0.

 

[Moregelen]

You will need to go grab the object numbers (the numbers shown to the left of the name) of the Menu Item classes and run pmegan's script above using those; the X, Y, and Z.

So, POS Configurator - > Sales. Click Menu Item Classes and then find the three menu item classes. Write down their object numbers.

https://storage.kotrs.com/public.php?service=files&t=c0d655fe1b49aad2b940841e82d75800

Once you have all of the prices you need everything to change to input, we can discuss how to activate/deactivate those prices.

 

[NorNev]

Just a clarification.
I can grab all of the Menu Item Class object numbers that need to be changed in the one query?
I ask this as I need to change BEER at the same time as LIQUOR. The price bump with be the same, $1.00 across the board.

 

[Moregelen]

Ah. If all you are doing is increasing the price by $1.00 across the board, yes you can do it in one query.

 

[NorNev]

Query has been run and Price 1 has been copied into Price 5 and Price 6.
Menu Items have been reviewed and all appears good.

 

[pmegan]

It's kind of strange that you seem to be running on level 2 all the time, but that doesn't really matter.
It doesn't make sense to have Well Rum multi priced if you're always charging 3.25 for it. If your certain that you're never charging the other prices just change the menu item class to whatever the other rums are using and change price 1 to 3.25.

You can dump out the beer prices along with the liquor by adding a comma and the beer menu item class number to the (x, y, x) list. (so it would be (x, y, z, b)).

Also, if you've got a lot of items that are anly being changed by $1, you can change "preset_amt_5 = preset_amt_1" to "preset_amt_5 = preset_amt_1 + 1". That will copy all the prices plus $1 to price 5 and you'll only have to work on the ones that are changing by something other than $1.