T-SQL script to update A/P account value of Vendor Card

Hello:

For some reason, I just noticed that a question that I posed over an hour 
ago still has not posted to the newsgroups.  So, I'm going to try to repost, 
here.

I am trying to develop a simple T-SQL script in Fabrikam where I update the 
A/P account of the vendor card with a different account number from what is 
presently there.  When I try each of the scripts below, SQL returns 0 
results.  Why?

UPDATE PM00200 
SET PM00200.PMAPINDX = GL00100.ACTINDX
from PM00200 as PM00200
join GL00100 as GL00100
on PM00200.PMAPINDX = GL00100.ACTINDX
WHERE PMAPINDX IN(
select ACTINDX from GL00100 where ACTNUMBR_1 = '000' AND ACTNUMBR_2 = '2105' 
AND ACTNUMBR_3 = '00')
and PM00200.VENDORID LIKE 'ACE%'


UPDATE PM00200 
SET PM00200.PMAPINDX = GL00100.ACTINDX
from PM00200 as PM00200
join GL00100 as GL00100
on PM00200.PMAPINDX = GL00100.ACTINDX
where Gl00100.ACTNUMBR_1 = '000' AND GL00100.ACTNUMBR_2 = '2105' AND 
GL00100.ACTNUMBR_3 = '00'
and PM00200.VENDORID LIKE 'ACE%'

childofthe1980s
0
Utf
1/5/2010 5:22:01 PM
greatplains 29623 articles. 6 followers. Follow

2 Replies
960 Views

Similar Articles

[PageSpeed] 42

Hi,

Your syntax is perfect.  I would reckon rephrase your logic with the 
following SQL script if you're updating ACETRAVE0001 with PM AP Account= 
'000-2105-00':

UPDATE PM00200
SET PM00200.PMAPINDX = (select GL00100.ACTINDX  from gl00100
where GL00100.ACTNUMBR_1 = '000' AND GL00100.ACTNUMBR_2 = '2105' and 
ACTNUMBR_3 = '00')
from PM00200 As PM00200
INNER JOIN GL00100 as GL00100 
ON PM00200.PMAPINDX = GL00100.ACTINDX
where PM00200.VENDORID = 'ACETRAVE0001'

Hope this helps!

Cheers,

Dennis Araullo, MACS
Microsoft Certified Technology Specialist GP,CRM,AX
MCITP Installation and Configuration AX 2009
MCITP Database Administrator SQL 2008
Citrix Certified Enterprise Administrator



"childofthe1980s" wrote:

> Hello:
> 
> For some reason, I just noticed that a question that I posed over an hour 
> ago still has not posted to the newsgroups.  So, I'm going to try to repost, 
> here.
> 
> I am trying to develop a simple T-SQL script in Fabrikam where I update the 
> A/P account of the vendor card with a different account number from what is 
> presently there.  When I try each of the scripts below, SQL returns 0 
> results.  Why?
> 
> UPDATE PM00200 
> SET PM00200.PMAPINDX = GL00100.ACTINDX
> from PM00200 as PM00200
> join GL00100 as GL00100
> on PM00200.PMAPINDX = GL00100.ACTINDX
> WHERE PMAPINDX IN(
> select ACTINDX from GL00100 where ACTNUMBR_1 = '000' AND ACTNUMBR_2 = '2105' 
> AND ACTNUMBR_3 = '00')
> and PM00200.VENDORID LIKE 'ACE%'
> 
> 
> UPDATE PM00200 
> SET PM00200.PMAPINDX = GL00100.ACTINDX
> from PM00200 as PM00200
> join GL00100 as GL00100
> on PM00200.PMAPINDX = GL00100.ACTINDX
> where Gl00100.ACTNUMBR_1 = '000' AND GL00100.ACTNUMBR_2 = '2105' AND 
> GL00100.ACTNUMBR_3 = '00'
> and PM00200.VENDORID LIKE 'ACE%'
> 
> childofthe1980s
0
Utf
1/6/2010 2:00:01 AM
Hi Dencio:

Thanks!  This worked perfectly!!!

Yeah, it looks like it was just a matter of replacing that second line of my 
script (SET PM00200.PMAPINDX = GL00100.ACTINDX) with what I actually wanted 
to change.

Again, thank you!

childofthe1980s


"Dencio" wrote:

> Hi,
> 
> Your syntax is perfect.  I would reckon rephrase your logic with the 
> following SQL script if you're updating ACETRAVE0001 with PM AP Account= 
> '000-2105-00':
> 
> UPDATE PM00200
> SET PM00200.PMAPINDX = (select GL00100.ACTINDX  from gl00100
> where GL00100.ACTNUMBR_1 = '000' AND GL00100.ACTNUMBR_2 = '2105' and 
> ACTNUMBR_3 = '00')
> from PM00200 As PM00200
> INNER JOIN GL00100 as GL00100 
> ON PM00200.PMAPINDX = GL00100.ACTINDX
> where PM00200.VENDORID = 'ACETRAVE0001'
> 
> Hope this helps!
> 
> Cheers,
> 
> Dennis Araullo, MACS
> Microsoft Certified Technology Specialist GP,CRM,AX
> MCITP Installation and Configuration AX 2009
> MCITP Database Administrator SQL 2008
> Citrix Certified Enterprise Administrator
> 
> 
> 
> "childofthe1980s" wrote:
> 
> > Hello:
> > 
> > For some reason, I just noticed that a question that I posed over an hour 
> > ago still has not posted to the newsgroups.  So, I'm going to try to repost, 
> > here.
> > 
> > I am trying to develop a simple T-SQL script in Fabrikam where I update the 
> > A/P account of the vendor card with a different account number from what is 
> > presently there.  When I try each of the scripts below, SQL returns 0 
> > results.  Why?
> > 
> > UPDATE PM00200 
> > SET PM00200.PMAPINDX = GL00100.ACTINDX
> > from PM00200 as PM00200
> > join GL00100 as GL00100
> > on PM00200.PMAPINDX = GL00100.ACTINDX
> > WHERE PMAPINDX IN(
> > select ACTINDX from GL00100 where ACTNUMBR_1 = '000' AND ACTNUMBR_2 = '2105' 
> > AND ACTNUMBR_3 = '00')
> > and PM00200.VENDORID LIKE 'ACE%'
> > 
> > 
> > UPDATE PM00200 
> > SET PM00200.PMAPINDX = GL00100.ACTINDX
> > from PM00200 as PM00200
> > join GL00100 as GL00100
> > on PM00200.PMAPINDX = GL00100.ACTINDX
> > where Gl00100.ACTNUMBR_1 = '000' AND GL00100.ACTNUMBR_2 = '2105' AND 
> > GL00100.ACTNUMBR_3 = '00'
> > and PM00200.VENDORID LIKE 'ACE%'
> > 
> > childofthe1980s
0
Utf
1/6/2010 12:51:03 PM
Reply:

Similar Artilces:

Customer Card and Collections
Customer: We are using Collection Management and Query Builder. I believe that when collection management letters are printed, the customer "notes" is updated to reflect that the letter was sent out. I have a query in which I want to identify a range of customers that are greater than 120 days. I don't want to create letters to the customers but I want to capture a summary of the customers that meet this criteria. I believe I can do this from the Collection Management Report (Aging Amount with Notes). However, I also want the customer card to be updated to reflect that the...

Money Can't Set up a Mortgage Right!
I have issues with Money 2003. I bought a house Nov 2003. I set up the mortgage account and tell it my purchase date. It starts the loan 1/1/2004 because it assumes I have paid Nov interest at closing and won't owe anything until 1/1/2004. This screws up my net worth reports because I had a home asset in Nov 03, but the loan liability doesn't appear until Jan 04. Broken! I can't seem to set the loan date to force it to recognize the Nov loan beginning date. What to do?? Thanks, Ben Money starts the loan in 1/1/2004 because that's the date you told it the first payment...

My mail merged document won't save the mailing info.
I have created a postcard in publisher 2002 and performed a mail merge. After Step 3 of 4 in the mail merge wizard, my addresses appear in the publisher file. Step 4 of 4 asks me to print. I want to save the file to upload to OfficeMax's website. So, I have tried both saving the file and also Pack and go..., however both options, when I save then reopen the file, my merged addresses do not appear. All I have is the templated post card or a blank file with the letters MSCF at the top. Help! Erin This can only be done with Publisher 2003 unless you choose to print your mailm...

How can I initialize the value?
template < class elemType > class MyArray { public: explicit MyArray( int size = DefaultArraySize ); MyArray( elemType *array, int array_size ); MyArray( const MyArray &rhs ); virtual ~MyArray() { delete [] ia; } bool operator==( const MyArray& ) const; bool operator!=( const MyArray& ) const; MyArray& operator=( const MyArray& ); int size() const { return _size; } virtual elemType& operator[](int index) { return ia[index]; } virtual void sort(); virtual elemType min() cons...

Cannot change account type.
I have an HSA with a credit union that is updated online, I want to have it listed under the Investment Accounts instead of Bank Accounts. The option to change the account type is greyed out with the message "...online-enable account cannot be changed" Was there a question here? I think the message you got tells you about three fourths of the problem. The final fourth that the first three fourths prevents you from getting to is that an Investment Account and a Bank Account are fundamentally different types--one holds Investments, the other holds cash--so a change from one ...

outlook won't automatically receive/send when opened
outlook won't automatically receive/send when opened how do i get it to do this? An answer would require knowing your Outlook version, mail account type, and connection type. -- Russ Valentine [MVP-Outlook] "newbiejw" <newbiejw@discussions.microsoft.com> wrote in message news:EDBE10B5-2D8E-4684-99A8-C8C0F4FDC25F@microsoft.com... > outlook won't automatically receive/send when opened > how do i get it to do this? ...

How do I import my address book from my POP 3 Juno account?
I'm hoping to import my address book from my juno email on the web account to Outlook, and then to export it to my new Hotmail account. I seem to be botching it badly, so far. I was able to save my list to 'Recent Documents'. My efforts to COPY and Paste as an individual 'New Contact' result in partial information and the email address, phone number, etc. are missing. Are there step-by-step instructions to accomplish what should be a relatively easy task? Also, it looks as though Outlook has stripped all of my mail from my juno account. How do I send it b...

How to update contacts in WLM via csv file?
I have nearly 2,000 email addresses in my contacts list. I managed to get WLM to list them, although I do notice some are missing. Now, I want to update them with new/revised contacts through a new cvs file, which I exported from OE. However, the contacts import program just hangs and doesn't take. Do I need to delete the existing list first? If so, how do I do that? I am using WLM v.2009 Build 14.0.8 on Windows 7 Are you logging in to Live services? It will say your profile name under the close X. -- Ron Sommer "Bumper21248" wrote in message news...

Get $500 to your PAYPAL Account by just one CLICK #2
i have hidden the form of getting $500 to your PAYPAL ACCOUNT....CLICK on the IMAGE which is on the RIGHT SIDE of the website http://tricks-4u.4-all.org/ ...

Windows Live Messenger doesn't "remember" me
Hi All, My Windows Live Messenger doesn't "remember" my username/ID. I click remember me and then when having signed off I restart it or the computer again, it doesn't "remember" me. Help! ...

Cascading account ownership to Order ownership
Hi, I have read the threads regarding cascading ownership and changing the parental to cascading none. However on the Account Order there is no parental realtionship so I how would I go about preventing the ownership of the Order changing with the account owner changes. ...

Scripting the Great Plains installation
This topic originally started when I went looking for a way to script the installation of Great Plains v8. There is quite a bit of redundant data entry during the installation process, and I was looking for a way to automate the install. Seeing how difficult it is to script application installation is my normal precursor to rollout using Microsoft Systems Management Server. I took a look at the install files on the Great Plains 8 CD, and noticed there was an InstallShield Setup.exe. Executing the file for installation fails however, with the warning “Please use CDSetup.exe to launch the...

Complex (for me) checking values in fields to perform calcs in others
Hi all.. I am creating a Cash Flow Projection report in access that inspects the "completed dates" of sheduled draws to calculate a remaining balance, but I am having problems with it. The idea is: --------------------- Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND [Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amoun...

How to convert a Chinese registry value from within a US code page?
First of all, our Win32 MFC VC++ app is not written in Unicode as it should have been. Given that - it reads uninstall information from the windows Uninstall registry tree and writes it to a report. It writes out the displayname of the application. Normally this is not a problem, but when it encounters a Chinese application name, it displays the name as question marks. I assume this is because the string is in Unicode in the registry, but our app is not Unicode so it can't represent the characters. Unfortunately for the moment, converting this very large app to Unicode is not an o...

Deleted Accounts in AD
Hello! I am trying to reduce store database sizes by emptying mailboxes of accounts deleted in Active Directory. I tried creating a profile in Outlook but cannot resolve the account name. Is there a way to do this without recreating the account and reconnecting to the mailbox? I would reduce the store sizes by approx. 2 GB. Thank you. -- JoeCL LACO-CAO if you have no need for the data in the mailboxes, just purge them... -- Susan Conkey [MVP] "JoeCL" <JoeCL@discussions.microsoft.com> wrote in message news:1BA4EE14-82E1-42E7-96F3-F79BA50B5793@microsoft.com... >...

XP Machines Don't work after Win7 crash
Well this has happened twice now where the XP Virtual Machines will not work after either a WIN7 crash or an auto reboot when updates are installed. Today, Windows was stalled or running so slow that I couldn't get into the virtual machines to shut them down for a re-boot. I had to force a logoff. When I tried to use a shortcut to startup an XP Virtual machine it wouldn't do anything when I clicked on it. It re-acted the same way when I clicked on the vmc file itself. Fortunately I had a backup of the machine. The first time this happened was when automatic updates we...

Apply this security update
--qzhgrregdv Content-Type: multipart/related; boundary="fimwsrpzzjzrq"; type="multipart/alternative" --fimwsrpzzjzrq Content-Type: multipart/alternative; boundary="nihpkigvdw" --nihpkigvdw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer 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 as well as three newly discovered vulnerabilities. Install now to ma...

Default Value problem 02-22-08
Feel a bit silly. I want to insert a =Now() Date field on every new record created via a certain form. I don't want people seeing this info. So, I added AphaDate Date field to the table the query for this form is based on. I insert the field on the form and mark it hidden. In the properties for this field on the form, I say the default value =Now() I assume then that any new record created will "stamp" with this date. But it doesn't work When I create the table, add the field to the query and try to set this up, the field shows up as blank no matter what I do. Wha...

Exporting e-mail account settings
I want to transfer all my account settings to another computer, also running Outlook 2003. How do I do this? Many thanks Tony Not sure what operating system you are using, but I tend to using Windows XP's Files and Setting Transfer Wizard and Microsoft Office's 2003 Save My Settings Wizard to transfer settings from one system to the next. Another possibility is the user state migration tool. http://www.microsoft.com/downloads/details.aspx?FamilyId=4AF2D2C9-F16C-4C52-A203-8DAF944DD555&displaylang=en "Tony Rush" <NOtony_w_rushSPAM@btopenworld.com> wrote i...

downloads from credit cards?
Does Money 2002 allow you to download your transactions from credit card companies such as Discover, MasterCard, etc.? -- Jeff Malka malkajef@orthohelp.com Money (02 among others) supports it. Many financial institutions support it. Some provide better data than others. Many swear by using downloaded transaction data. If you follow the NG for a while, you will also find that many people have more problems keeping it all working than it can possibly be worth. I Just Say No to downloaded transaction data. YMMV. "Jeff Malka" <malkajef@orthohelp.com> wrote in message news:eK...

Dialog text only updated on mouse moves
My PreTranslateMessage() function has code in it to update some of the text in one of my dialog boxes. It works, but instead of updating it every second, it only udpates the text when my mouse is moving or clicking. Any ideas on what might be causing this? Thank you! Well, that's becuase PreTranslateMessage gets called only when there is message in the queue for that window. Instead use a timer. See OnTimer (WM_TIMER) AliR. <PloutzMR@Npt.NUWC.Navy.Mil> wrote in message news:1154456910.215194.170930@s13g2000cwa.googlegroups.com... > My PreTranslateMessage() function has c...

Increment Account Numbers
Hello, I'm trying to figure out a way to generate account number and increment them based on dates. For example: A-Account Number B-Date 1 P010110-01 01/01/10 2 P010110-02 01/01/10 3 P010210-01 01/02/10 4 P020310-01 02/03/10 5 P020410-01 02/04/10 6 P020410-02 02/04/10 7 P020410-03 02/04/10 So in column A the account number is generated by the date entered in column b. If the date enter is 0...

Money Express doesn't allow Deposits and Withdrawals
I have Money 2004 installed on Windows XP Professional. I'd like to use Money Express to enter deposit and withdrawal transactions, but these menu items are disabled. When I right-click the money express icon on the task bar, I see a menu option to "Add a New Withdrawal", but the item is gray and disabled. This item becomes enabled when I launch the full Money application, but that defeats the purpose of quick transaction entry. I vaguely remember searching for a solution to this problem months ago, and I think I found something that suggested the problem is related to ...

Doing a VLOOKUP (probably using the INDEX and MATCH function), with both vertical and horizontal values in play.
I am trying to create a function that will pull in data from a 2nd spreadsheet. Typically, I use the index and match function to do so. However, in this case, I am trying to do a lookup based on a value above (i.e., horizontal) and a value to the right (i.e., vertical) of the cell in which the formula will be placed. Additionally, the sheet from which I am pulling is similarly laid out. To Provide an example. Lookup Table Months (horizontal) Jan Feb Mar Apr Etc Names(vertical) Jeff Eric 5 Steve ...

Move downloaded transactions to another account?
For some reason, the downloaded transactions for one account have appeared in another account. I don't see any way to move these to the correct account. Is this possible to do in Money 2008? in 2007 right click on the transaction and copy and paste to different account, not sure about 08. "Noozer" <dont.spam@me.here> wrote in message news:%23JYlJMq3HHA.4672@TK2MSFTNGP05.phx.gbl... > For some reason, the downloaded transactions for one account have appeared > in another account. I don't see any way to move these to the correct > account. > > I...