trigger to update last sold date in HQ item table

hi ,
i need to update automaticaly the last sold date in HQ from last sold date 
in store (since it is not updated from stores).
can we use item dynamic table to update item table ? if so can any one give 
a trigger to do that.
thanks
0
Sad1 (39)
1/28/2009 3:26:13 PM
pos 14173 articles. 0 followers. Follow

5 Replies
523 Views

Similar Articles

[PageSpeed] 19

I have no idea about HQ

However, in my opinion creating trigger can slow down database executions. 
Be careful with that

May be it would be better if you create a stored procedure to contain the 
update logic and then schedule the stored procedure to run hourly or daily 
?

rgds,
Joie


"Sad" <Sad@discussions.microsoft.com> wrote in message 
news:EACA390D-F318-48EA-A852-7448CD8B6158@microsoft.com...
> hi ,
> i need to update automaticaly the last sold date in HQ from last sold date
> in store (since it is not updated from stores).
> can we use item dynamic table to update item table ? if so can any one 
> give
> a trigger to do that.
> thanks 


0
joie (60)
1/28/2009 3:36:26 PM
thank you joie
i am a biggener in sql, can you help me with that please.

"Joie" wrote:

> I have no idea about HQ
> 
> However, in my opinion creating trigger can slow down database executions. 
> Be careful with that
> 
> May be it would be better if you create a stored procedure to contain the 
> update logic and then schedule the stored procedure to run hourly or daily 
> ?
> 
> rgds,
> Joie
> 
> 
> "Sad" <Sad@discussions.microsoft.com> wrote in message 
> news:EACA390D-F318-48EA-A852-7448CD8B6158@microsoft.com...
> > hi ,
> > i need to update automaticaly the last sold date in HQ from last sold date
> > in store (since it is not updated from stores).
> > can we use item dynamic table to update item table ? if so can any one 
> > give
> > a trigger to do that.
> > thanks 
> 
> 
> 
0
Sad1 (39)
1/28/2009 3:50:05 PM
Hi Sad - my first suggestion would be to (if its in the budget) work with a 
RMS-certified partner that can help you build and maintain SQL queries....as 
SQL is nice to use but it can be tricky to manage.  Joie makes a good point 
regarding triggers, as they can affect the performance of your database.  
Here's one possible option you can look into (without recurring to a trigger)

The problem with the itemdynamic table is that it tracks several "last sold" 
dates, one for each store, so you have to "pick" the most recent "last sold" 
date.  

I would first create a view that summarizes for you the "last sold" date for 
all stores, then join that view to your item table on an update query that 
you would run as a batch file via the windows scheduler program.

Here is the syntax for the view:

create view item_last_sold 
as 
select id.itemid, i.itemlookupcode, MAX(id.lastsold) as last_sold
from itemdynamic id 
inner join item i on id.itemid = i.id 
group by id.itemid, i.itemlookupcode

Test the successful creation of the view by running
select * from item_last_sold

Here is the update query you can save on a notepad, save it as a .bat file, 
then schedule the .bat file to run after your stores poll with HQ, using the 
windows scheduler:

update item 
set item.lastsold = item_last_sold.last_sold 
from item  
inner join item_last_sold on item.id = item_last_sold.itemid

If you can, test these on a testing database first, and remember to backup 
your database prior to running the queries.

But, really, this is something that Mickeysoft MUST fix on the next version 
of RMS...hope this helps.

"Sad" wrote:

> thank you joie
> i am a biggener in sql, can you help me with that please.
> 
> "Joie" wrote:
> 
> > I have no idea about HQ
> > 
> > However, in my opinion creating trigger can slow down database executions. 
> > Be careful with that
> > 
> > May be it would be better if you create a stored procedure to contain the 
> > update logic and then schedule the stored procedure to run hourly or daily 
> > ?
> > 
> > rgds,
> > Joie
> > 
> > 
> > "Sad" <Sad@discussions.microsoft.com> wrote in message 
> > news:EACA390D-F318-48EA-A852-7448CD8B6158@microsoft.com...
> > > hi ,
> > > i need to update automaticaly the last sold date in HQ from last sold date
> > > in store (since it is not updated from stores).
> > > can we use item dynamic table to update item table ? if so can any one 
> > > give
> > > a trigger to do that.
> > > thanks 
> > 
> > 
> > 
0
convoluted (621)
1/28/2009 7:02:03 PM
thank you all, that was helpfull.

"convoluted" wrote:

> Hi Sad - my first suggestion would be to (if its in the budget) work with a 
> RMS-certified partner that can help you build and maintain SQL queries....as 
> SQL is nice to use but it can be tricky to manage.  Joie makes a good point 
> regarding triggers, as they can affect the performance of your database.  
> Here's one possible option you can look into (without recurring to a trigger)
> 
> The problem with the itemdynamic table is that it tracks several "last sold" 
> dates, one for each store, so you have to "pick" the most recent "last sold" 
> date.  
> 
> I would first create a view that summarizes for you the "last sold" date for 
> all stores, then join that view to your item table on an update query that 
> you would run as a batch file via the windows scheduler program.
> 
> Here is the syntax for the view:
> 
> create view item_last_sold 
> as 
> select id.itemid, i.itemlookupcode, MAX(id.lastsold) as last_sold
> from itemdynamic id 
> inner join item i on id.itemid = i.id 
> group by id.itemid, i.itemlookupcode
> 
> Test the successful creation of the view by running
> select * from item_last_sold
> 
> Here is the update query you can save on a notepad, save it as a .bat file, 
> then schedule the .bat file to run after your stores poll with HQ, using the 
> windows scheduler:
> 
> update item 
> set item.lastsold = item_last_sold.last_sold 
> from item  
> inner join item_last_sold on item.id = item_last_sold.itemid
> 
> If you can, test these on a testing database first, and remember to backup 
> your database prior to running the queries.
> 
> But, really, this is something that Mickeysoft MUST fix on the next version 
> of RMS...hope this helps.
> 
> "Sad" wrote:
> 
> > thank you joie
> > i am a biggener in sql, can you help me with that please.
> > 
> > "Joie" wrote:
> > 
> > > I have no idea about HQ
> > > 
> > > However, in my opinion creating trigger can slow down database executions. 
> > > Be careful with that
> > > 
> > > May be it would be better if you create a stored procedure to contain the 
> > > update logic and then schedule the stored procedure to run hourly or daily 
> > > ?
> > > 
> > > rgds,
> > > Joie
> > > 
> > > 
> > > "Sad" <Sad@discussions.microsoft.com> wrote in message 
> > > news:EACA390D-F318-48EA-A852-7448CD8B6158@microsoft.com...
> > > > hi ,
> > > > i need to update automaticaly the last sold date in HQ from last sold date
> > > > in store (since it is not updated from stores).
> > > > can we use item dynamic table to update item table ? if so can any one 
> > > > give
> > > > a trigger to do that.
> > > > thanks 
> > > 
> > > 
> > > 
0
Sad1 (39)
1/29/2009 6:56:06 AM
hi convoluted,
can i use this query directly without create a view?
update item 
set item.lastsold = MAX(itemDynamic.lastsold )
from item  
inner join item on item.id = itemDynamic.itemid
thanks

"convoluted" wrote:

> Hi Sad - my first suggestion would be to (if its in the budget) work with a 
> RMS-certified partner that can help you build and maintain SQL queries....as 
> SQL is nice to use but it can be tricky to manage.  Joie makes a good point 
> regarding triggers, as they can affect the performance of your database.  
> Here's one possible option you can look into (without recurring to a trigger)
> 
> The problem with the itemdynamic table is that it tracks several "last sold" 
> dates, one for each store, so you have to "pick" the most recent "last sold" 
> date.  
> 
> I would first create a view that summarizes for you the "last sold" date for 
> all stores, then join that view to your item table on an update query that 
> you would run as a batch file via the windows scheduler program.
> 
> Here is the syntax for the view:
> 
> create view item_last_sold 
> as 
> select id.itemid, i.itemlookupcode, MAX(id.lastsold) as last_sold
> from itemdynamic id 
> inner join item i on id.itemid = i.id 
> group by id.itemid, i.itemlookupcode
> 
> Test the successful creation of the view by running
> select * from item_last_sold
> 
> Here is the update query you can save on a notepad, save it as a .bat file, 
> then schedule the .bat file to run after your stores poll with HQ, using the 
> windows scheduler:
> 
> update item 
> set item.lastsold = item_last_sold.last_sold 
> from item  
> inner join item_last_sold on item.id = item_last_sold.itemid
> 
> If you can, test these on a testing database first, and remember to backup 
> your database prior to running the queries.
> 
> But, really, this is something that Mickeysoft MUST fix on the next version 
> of RMS...hope this helps.
> 
> "Sad" wrote:
> 
> > thank you joie
> > i am a biggener in sql, can you help me with that please.
> > 
> > "Joie" wrote:
> > 
> > > I have no idea about HQ
> > > 
> > > However, in my opinion creating trigger can slow down database executions. 
> > > Be careful with that
> > > 
> > > May be it would be better if you create a stored procedure to contain the 
> > > update logic and then schedule the stored procedure to run hourly or daily 
> > > ?
> > > 
> > > rgds,
> > > Joie
> > > 
> > > 
> > > "Sad" <Sad@discussions.microsoft.com> wrote in message 
> > > news:EACA390D-F318-48EA-A852-7448CD8B6158@microsoft.com...
> > > > hi ,
> > > > i need to update automaticaly the last sold date in HQ from last sold date
> > > > in store (since it is not updated from stores).
> > > > can we use item dynamic table to update item table ? if so can any one 
> > > > give
> > > > a trigger to do that.
> > > > thanks 
> > > 
> > > 
> > > 
0
Sad1 (39)
1/29/2009 8:06:03 AM
Reply:

Similar Artilces:

windows update kb976321 and kb981726
i have tried to install these several times but they keep failing. i get error code 646 but nothing seems to help. what else can i do to fix this? DiamondBailey wrote: > i have tried to install these several times but they keep failing. i get > error code 646 but nothing seems to help. what else can i do to fix this? For Office updates, the 646 error is usually resolved by inserting the Office CD *prior* to the attempted installation of the updates or by manually downloading the full file versions of the updates from the MS Download Center and then installing them manually...

Bill Calendar update problem
I have my bills setup to remind me at a months glance when they are due. (not billpay) But when I auto update my accounts (eg..checking) and it see's a transaction from my bank that is close to the date, and amount of a bill...it automatically assumes this is a bill and enters it as such in the account register. I want to manually enter in Bills. This seems to happen mostly with Cash withdrawls, than anything else. Any help would be appreciated. -JW ...

ForeFront update to SP2
Hi, Yesterday updated Forefront 2007 to SP2. On Exchange 2007 SP2 Edge servers all works fine. On Mailbox role CCR cluster - can't download updates, in application log found messages "license expired" , but when looked for license in Forefront administrator - license still valid until 2013 :) Small - but problem, still searching for solution :() Maybe someone have some ideas ? Diamondas I have the same problem - any solutions out there? Diamondas wrote: ForeFront update to SP2 - license expired :( ? 11-Dec-09 Hi, Yesterday updated Fore...

Disregard last entry
I hit the wrong group ...

Incorrect dates when copying to a new workbook??
Hello. I'm hoping someone else has encountered this problem and knows how to fix it. I'm working with some older spreadsheets that have continually been updated for many years now. When I try to copy the sheet to a new book or just copy the cells of dates and paste them into a different workbook, the dates are behind by exactly 1,462 days. For example, I'll copy 12/31/03 into a new work book and it will appear as 12/30/99. How do I fix this? Thank you! Corey Hi Corey! You appear to have your new setting at the 1904 date system: Use: Tools > Options > Calculation...

Trigger email
Is there a way that you can have a countdown and when the countdown reaches 0 for it to automaticly send out a email to a certian person? You will first have to determine how you are going to calculate your countdown. Then try the following: Dim strSubject As String Dim strAddress As String Dim strMessage As String Dim blSendEmail as Boolean strSubject = "Countdown reached zero" strMessage = "This is the message that will be the body of the email." If 'This is where you would put the countdown check value' Then ...

Rob...Cost update trigger
I found a post that originally included a cost update trigger that Rob had created. It updates the sales price based on a new cost. I can't get the attachment anymore. Anybody out there have the trigger? Thanks, G E ...

Update Results
Hi All - I have a problem that I'm hoping someone may know how to answer. When I startup my computer in the evening (I have Windows XP SP2, 3GB of RAM and a 1TB HDD, with a 1.5Mb DSL connection), if I startup Money first before any other progs that access the Internet, Money can update all my accounts with no problems. But if I startup Outlook to look for emails, and Firefox (I have maybe 30 tabs that it opens), then do update in Money, I inevitably get errors when downloading the updates. I always have to repeatedly do Update before all the accounts update error free (banking, cr...

Taste this internet update from the Microsoft
--jowssxokvo Content-Type: multipart/related; boundary="jukqxrdabzx"; type="multipart/alternative" --jukqxrdabzx Content-Type: multipart/alternative; boundary="ygcghhjyswr" --ygcghhjyswr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vulner...

Daylight Saving Time update
DST is fast approaching. Sunday, March 11, 2007 Read " Is your Mac ready for Daylight Saving Time (DST)?" <http://www.entourage.mvps.org/articles/daylight.html> Entourage X users also see: <http://www.entourage.mvps.org/articles/unofficial_dst_workaround.html> Some notes for exchange users that are experiencing problems after updating: * Were your events created with v11.3.3, an earlier version of Entourage, or even another client? I have found that v11.3.3 handles just about every case properly, with or without the Mac OS X Tiger updates (i.e. with 10.4.4 as well...

Trigger
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is not being updated, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT If I do the below trigger, if I don't update DataFormat, it sets it correctly, but...

Dates for the whole month
Hi, Team Amount Date ------- --------- ------ Blue 100 01/03/2008 Blue 200 02/03/2008 Green 500 01/03/2008 Green 400 02/03/2008 How can i sum the amounts for each team. So everyday the Amount field will include the amount from the previous date. Basically i want a comulative figure for the month for each Team. Thanks for your help mahmad, If your Date field/column has a date/time data type, then something like this might work: SELECT DateSerial(Y...

no updates available
I recently re-loaded my XP SP3 and activated it through the microsoft process. The original cd was 4 1/2 yrs old so I know that many critical updates are needed. However, everytime I check windows update, it reports that no critical updates are available for my computer. Does anyone know what the problem must be??? -- PROINWV You say you have re-loaded XP SP3? The Service Pack 3 isn't that old; maybe about fourteen months or so; if you have SP3 installed, and then installed the critical updates since SP3, you might indeed be up to date now. :) "proinwv" w...

Date field behavior differs between forms
I have two forms with seemingly identical date fields. In one, if I place my cursor in the middle of it, the first number I type gets put in the far left of the field. In the field on the other form, if I place the cursor in the middle, it starts typing right where I am. Any ideas? I have thoroughly looked through the properties of each field and each form and I cannot find what is causing this behavior. I would love to get both of them to start placing typed characters at the far left instead of where the cursor is. Thanks! ...

Crystal Report dates
Hi, Our software is RMS and we have Crystal Reports (brand new). We're trying to run a report but the date fields are screwing us up. Help! -- Thanks,Jan Jan, I don't have much to go on with your post, but RMS is very particular to the version of Crystal Reports that it runs internally. CR 8.5 is the default version that *should* work with RMS. Some have said that even then, the def files sometimes need a little tweaking. I, have 8.5 Dev and 11 (XI) Dev, and have used both in different methods, 11 as an externally called VB routine. What do you mean by "screwing us" up?...

Need help updating 1 cell from other cells in same column
Hi and thanks in advanced for your attention. I am trying to do the following: Have cell B12 be updated everyday automatically from 1 different cell when data is found on those cells. Those cells contain a formula that sums 3 cells in one same row. The total is of course put on those cells (B16 - B35). To specify more on this problem let me give in that: B16 represents the money earned on February 1st. B17 represents the money earned on February 2nd B18 the same for Feb. 3rd. and so on. That data is only entered once per day. I tried link B12 to the value on those cells, but ...

XML DSO data island using DTD does not display correct date format
Background : with XML data islands you can bind data to HTML tags. If you do not provide a DTD it will display the information as is. If you provide a DTD IE will display the data according to its datatype. The most significant is dates, by providing the date in the proper XML format IE will display the date in a nice readable format. But being in Ausrtralia I am expecting my date format to be displayed like so : dd/mm/yy It is displaying the dates in American format : mm/dd/yy My regional settings are set to Australian, so why are the dates still displaying in the wrong format ? ...

could not unregister BLM trigger
Hello: One of our Dynamics GP 9.0 clients is experiencing an unusual error message when exiting Report Writer and returning to GP. Although the error message is not priventing them from using Report Writer or GP, it is a point of interest. And, I would like to see what can be done to eliminate this message. The message is "Could not unregister the BLM trigger." Does anyone know what this means or how to eliminate it? I do not know, as of yet, if all workstations get this message or if even only just some users get this message. Thanks! childofthe1980s Hi I would sugg...

Would like to add 20 working/weekdays to a date
Ideally I would like a formula that will add 20 working days (British calendar) to a date in another cell. I don't know if this is possible so as an alternative, what would the formula be for 20 weekdays? Many thanks, Pete Look at WORKDAY, it is not a native excel function but it comes with excel/office and it can be either installed when you first install office/excel or it can be added later =WORKDAY(A1,20,Holidays) where A1 is the start date, 20 is the workdays and Holidays is range with public holidays =WORKDAY(A1,20,H1:H11) where you would put each holiday date in H1:H11 if y...

Outlook 2003 Attachment Printing Last
In outlook 2003 if you set a rule to print a bunch of emails or if you highlight 15 emails where some have attachments and some do not it prints all the emails first then the attachments at the back, out of order. Is there any way to get them to print in order? So It goes -email1-email2-attachment to 2nd email-email3-email4 --MCspec No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ ...

SQL Triggers in Headquarters
We are planning on implementing RMS and Headquarters for our multiple-store retail sales (do not have it installed yet). We are also going to have a virtual store, which will sell items from our brick-and-mortar store inventory. Without getting into too many details at this time, we have determined that the best way for us to accomplish our business needs is to have an external SQL database which will be used to keep track of brick-and-mortar store inventory and will also be used to record virtual store sales. We would then have triggers on this database and on the Headquarters databas...

Error: Can't clean up the following tables: PriceLevel. 05-31-05
I cannot start CRM application. In error log ther is message Error: Can't clean up the following tables: PriceLevel. I tried to delete records with deletionstatecode = 2 in SQL and restarted SQL and CRM services bun nothing helps. Can someone help me? Thanks. Normally you don't have to directly interact with CRM Databases : it's not supported and can act on the MS CRM good operation. "Mikkhail" wrote: > I cannot start CRM application. In error log ther is message Error: Can't > clean up the following tables: PriceLevel. I tried to delete records with &...

updating spreadsheet from another spreadsheet
i need to update spreadsheet(B) from a seperate spreadsheet(A) that is on a share drive. i only want information that has been update, added, ect from (A) to populate (B). With a color coded cell (yellow) showing that the data from (A) is new when opening or manually running the macro on (B). (A) is a master spreadsheet that i need only a few of the columns data to populate (B). i am using Excel 2003 thanks also wanted to add that spreadsheet (B) will have data getting inputed on it and i don't want that over written when new or updated data is entered on spreadsheet (A). ...

Update from Commerce Bank NJ/PA/NY/DE
Money users, I contacted my bank regarding my inability to perform updates since some of their security measures have changed. We tested online and he saw my incoming connection and determined that Money did infact recieve the OFX transfer, however the update fails. Is anyone else out there having problems with MS Money 2007 and Commerce Bank NJ/PA/NY/DE? I had this successfully working for years now, but now it doesn't update at all. My only method of udpating my account information in Money is to login to their web application and then download the OFX file and then manually i...

MS Money 2000
I bought my copy of Ms Money 2000 whilst in the US. I moved back to the UK and changed the base currency to GBP. Now when I try to update stock quotes, I get a response that says I have to re-install. I have the original CD but I am not sure if the problem will go away. Anybody have a solution that will enable me update my quotes? Other aspects work well. -- I2CanWin I know I have a brain I just can't lay my hands on it ...