A customer with an integration to SOP was encountering a problem where SOP was insisting on a COGS integration for a Return document with an item that is a "Misc Chg" in inventory.
When they first saw the document it had 4 distribution lines, one each for the RECV and SALES entries, the other two for RETURNS and COGS. They claim the RETURNS line was blank with respect to account, but that there should rightly be no cost associated with this type of transaction.
When I manually created a SOP Return transaction in GP for the same vendor and item there was no COGS related entry. When they tried to "Default" the distribution the RETURNS and COGS lines were removed but they got:
"This Cost of Goods Sold distribution amount is incorrect and will not be posted. Do you want to save with errors?"
According to the KB posted here this is the result of a non-zero cost in either the EXTDCOST or OREXTCST fields for this transaction in the SOP tables.
Sure enough there was a cost amount in those fields, and that happened to be the Current Cost from that IV item. My guesstimation is the integration saw that IV cost and completed those cost columns in SOP, thus forcing the error.
Zeroing out that cost in the SOP tables per the KB solved the issue. I also identified one other Misc Chg IV item with a cost and advised they zero those cost values.
I'm a former Microsoft Dynamics consultant specializing in SQL Server integrations, reports and customizations.
Wednesday, May 28, 2014
Friday, May 23, 2014
Dynamics GP Smartlist Builder not upgrading
We have seen this before and here it is again: When we upgrade a client from one GP version to the next their Smartlists go awry. Last time I recall this was a GP 10 to GP 2010 upgrade.
In my current upgrade I am moving a client from GP 2010 to GP 2013. When I open Smartlist Builder in the new version their custom Smartlists are all there but no definition is visible for any of them. The Smartlists show the columns but no data.
The simple workaround we found in the previous upgrade is to export all the custom Smartlists from the old version then import them to the new version. This worked for my GP 2013 upgrade.
Besides it's a good idea to have an export of custom Smartlists anyway as a backup.
Oh and a reminder that Smartlist Builder has been moved from GP to eOne effective 1/1/14. Apparently this will drop from the MS enhancement renewal and be picked up by way of an eOne maintenance billing. Because we can never have enough customer confusion. </sarcasm>
In my current upgrade I am moving a client from GP 2010 to GP 2013. When I open Smartlist Builder in the new version their custom Smartlists are all there but no definition is visible for any of them. The Smartlists show the columns but no data.
The simple workaround we found in the previous upgrade is to export all the custom Smartlists from the old version then import them to the new version. This worked for my GP 2013 upgrade.
Besides it's a good idea to have an export of custom Smartlists anyway as a backup.
Oh and a reminder that Smartlist Builder has been moved from GP to eOne effective 1/1/14. Apparently this will drop from the MS enhancement renewal and be picked up by way of an eOne maintenance billing. Because we can never have enough customer confusion. </sarcasm>
FP: Script not found
Today was some Dexterity work. I'm updating a Dynamics GP Dexterity customization from GP 2010 to GP 2013 and received this error when opening one of my custom windows.
I spent almost an hour examining every script and reference in the form that was throwing the error. When I finally woke up and searched for the error I found the recommendation to compile the entire dictionary.
Lo and behold I was missing a table reference in another completely unrelated form customization. When I provided that table reference there the other form opened with no error.
So odd....
I spent almost an hour examining every script and reference in the form that was throwing the error. When I finally woke up and searched for the error I found the recommendation to compile the entire dictionary.
Lo and behold I was missing a table reference in another completely unrelated form customization. When I provided that table reference there the other form opened with no error.
So odd....
Wednesday, May 21, 2014
GP Revenue and Expense Deferral Tables
Long an overlooked add-on product for GP, I recently have been drawn to it by a new client acquisition. They are using it extensively in an integration to SOP from CRM (they use CRM for invoicing their customers and the results integrate to SOP.) As this integration and deferral is to SOP I will focus only on deferrals as related to SOP transactions.
The integration code that is operational today, which was written by a previous consultant, writes directly to four GP deferral tables.
PP000011 Deferral Profiles Header WORK
PP000042 Deferral Profile Allocated
PP000100 Deferral Header Work
PP000101 Deferral Line Work
Of course the related SOP table for all this data is SOP10102, which is the SOP Distribution Work and History table. As you may know deferrals are made off the transaction distribution (window) in GP.
The above tables link to each other based on the following common columns:
PP_Module - This is 5 for SOP.
PP_Record_Type - This correlates to the SOP Type, which is 3 for Invoice.
PP_Document_Number - This is the invoice/document number in SOP.
PPOFFSEQ - This seems to correlate to the SEQNUMBR column in SOP10102.
PP_Sequencer - This appears to be a sequence number for the deferrals.
Here's my take on the contents of the above table, again with respect to SOP integrations, subject to change as my understanding grows. :)
PP000042 Deferral Profile Allocated
This table appears to be the "master" deferral record, as it does no more than add the Deferral Profile column to the other linking columns.
PP000011 Deferral Profiles Header WORK
This table grabs the deferral profile information from the Deferral Profiles Header table PP000010.
PP000100 Deferral Header Work
This table contains the controlling header information for the deferral including the total amount of the deferral and the start/ending dates.
PP000101 Deferral Line Work
This table is the detail period deferral that is created for the transaction, which will post the GL.
Again I will update this with more detail as I can.
The integration code that is operational today, which was written by a previous consultant, writes directly to four GP deferral tables.
PP000011 Deferral Profiles Header WORK
PP000042 Deferral Profile Allocated
PP000100 Deferral Header Work
PP000101 Deferral Line Work
Of course the related SOP table for all this data is SOP10102, which is the SOP Distribution Work and History table. As you may know deferrals are made off the transaction distribution (window) in GP.
The above tables link to each other based on the following common columns:
PP_Module - This is 5 for SOP.
PP_Record_Type - This correlates to the SOP Type, which is 3 for Invoice.
PP_Document_Number - This is the invoice/document number in SOP.
PPOFFSEQ - This seems to correlate to the SEQNUMBR column in SOP10102.
PP_Sequencer - This appears to be a sequence number for the deferrals.
Here's my take on the contents of the above table, again with respect to SOP integrations, subject to change as my understanding grows. :)
PP000042 Deferral Profile Allocated
This table appears to be the "master" deferral record, as it does no more than add the Deferral Profile column to the other linking columns.
PP000011 Deferral Profiles Header WORK
This table grabs the deferral profile information from the Deferral Profiles Header table PP000010.
PP000100 Deferral Header Work
This table contains the controlling header information for the deferral including the total amount of the deferral and the start/ending dates.
PP000101 Deferral Line Work
This table is the detail period deferral that is created for the transaction, which will post the GL.
Again I will update this with more detail as I can.
Tuesday, May 20, 2014
Launch a URL from a button in SL pt 2
As I explained in part 1 a client required a means to link an SL AP document to its source location by way of a URL. I am doing this by adding a button to the SL AP screen that will open the URL location allowing the user to see the source transaction.
The hard part of this (if you don't create VBA customizations for SL every day) is the VBA code required to retrieve and launch the URL. We are storing the referring document ID in the AP "user" field in SL but we have to reach back to the source database to get the URL of that document. In part 1 I mentioned that a stored procedure returns the URL for a given document ID.
So we have the document ID on the SL AP screen in a user field. A button on the screen should open the source document from the provided URL when clicked by the user.
Here is a basic run-down of the steps required in the code:
In step 2 we run the stored procedure with the SqlFetch1() function. This function returns an integer status code we capture that should be zero or else we need to alert the user something went wrong in SQL. Function parameters for SqlFetch1 include:
Also in this Module is the Global declaration of the bTable1 instance of the data structure, as well as the ShellExecute() function required to launch the URL.
Note that VBA requires that each element of the data structure be explicitly matched to that of the values returned by the stored procedure (same holds true for SQL SELECT from views.) In our case the URL is of SQL NVARCHAR(MAX) - for which I'm not familiar with a related type in VB. So my workaround was to CAST the return value down to 200 characters - with the attendant risk that an extra-long URL might get cut off (right now none of the test data URL exceeds 100.)
So if the stored procedure works and we get a good URL back then we launch the URL with the ShellExecute() function. We use the "open" operation and pass the URL as the "file" parameter in the function. Up pops the originating document reference in the users browser.
How easy is that?
The hard part of this (if you don't create VBA customizations for SL every day) is the VBA code required to retrieve and launch the URL. We are storing the referring document ID in the AP "user" field in SL but we have to reach back to the source database to get the URL of that document. In part 1 I mentioned that a stored procedure returns the URL for a given document ID.
So we have the document ID on the SL AP screen in a user field. A button on the screen should open the source document from the provided URL when clicked by the user.
Here is a basic run-down of the steps required in the code:
- Get the document ID from the value of the user field on the AP screen.
- Run the stored procedure with the document ID provided to obtain the URL of the source.
- Open the URL in the default browser.
In step 2 we run the stored procedure with the SqlFetch1() function. This function returns an integer status code we capture that should be zero or else we need to alert the user something went wrong in SQL. Function parameters for SqlFetch1 include:
- Cursor: An integer variable that is initialized in the Form_Load event code with a "Call SqlCursor" statement.
- SqlStr: The SQL code to execute. For this instance it is the stored procedure name with the document ID variable concatenated to it with the SParm() function.
- bTable1: The data structure that receives the results of the stored procedure.
- bTable1Length: The length of the data structure that receives the results.
Also in this Module is the Global declaration of the bTable1 instance of the data structure, as well as the ShellExecute() function required to launch the URL.
Note that VBA requires that each element of the data structure be explicitly matched to that of the values returned by the stored procedure (same holds true for SQL SELECT from views.) In our case the URL is of SQL NVARCHAR(MAX) - for which I'm not familiar with a related type in VB. So my workaround was to CAST the return value down to 200 characters - with the attendant risk that an extra-long URL might get cut off (right now none of the test data URL exceeds 100.)
So if the stored procedure works and we get a good URL back then we launch the URL with the ShellExecute() function. We use the "open" operation and pass the URL as the "file" parameter in the function. Up pops the originating document reference in the users browser.
How easy is that?
Launch a URL from a button in SL pt 1
Being the king of integrations (kind of like being the king of your bike - bad analogy I know) at my job I am adapting a prior integration (as is tradition) to SL for another client. That's a long story in itself but not the tale I want to tell. This integration brings AP vouchers into SL from a new, SharePoint front-end another outfit is creating for the client.
One of the requirements of this integration is a button on the AP screen that takes the user to the document that originated the transaction. There is a column in the data source that contains the URL that points to the source document. When the user looks at this document on the AP screen in SL they want to click on the button and up pops the source document.
The key to the URL is the "document ID" from the source. That we store in a "user" field in the SL AP table. Unfortunately there is no place in the SL database to store the URL as it is stored in an nvarchar(max) column in the source SQL table and there is simply no place to contain that in SL.
Thankfully we have a customization from another outfit that launches a URL off another screen in the GL. That got me started but I need the updated Customization Manager for VBA reference manual and some help from the internet to get through it.
Here are the elements required to complete this assignment:
For the stored procedure in element 4 to work the SL 2011 Master80 user needs read access to the source database that contains the URL information. This stored procedure also needs to be created in each SL application database while the source data is in a single unrelated database on the SL SQL server. Master80 can execute the stored procedure in the SL database but not reach the data in the source without explicit access granted.
In my next post I will explain the components of the VBA code necessary to complete this assignment.
One of the requirements of this integration is a button on the AP screen that takes the user to the document that originated the transaction. There is a column in the data source that contains the URL that points to the source document. When the user looks at this document on the AP screen in SL they want to click on the button and up pops the source document.
The key to the URL is the "document ID" from the source. That we store in a "user" field in the SL AP table. Unfortunately there is no place in the SL database to store the URL as it is stored in an nvarchar(max) column in the source SQL table and there is simply no place to contain that in SL.
Thankfully we have a customization from another outfit that launches a URL off another screen in the GL. That got me started but I need the updated Customization Manager for VBA reference manual and some help from the internet to get through it.
Here are the elements required to complete this assignment:
- Locate the "user" field on the AP Screen (actually another requirement - the client wants to see this.)
- Add a button to the AP screen that takes the user to the related URL.
- VBA code behind the button.
- SQL stored procedure to return a URL for a given document ID.
For the stored procedure in element 4 to work the SL 2011 Master80 user needs read access to the source database that contains the URL information. This stored procedure also needs to be created in each SL application database while the source data is in a single unrelated database on the SL SQL server. Master80 can execute the stored procedure in the SL database but not reach the data in the source without explicit access granted.
In my next post I will explain the components of the VBA code necessary to complete this assignment.
Monday, May 19, 2014
Posting Date in SOP Open File
These are the types of questions you get when you are on vacation. :)
Client email came questioning the inconsistency of the GL Posting Date on Sales Order invoices in GP (2010.) Apparently some invoices have a posting date, others don't (as can be seen on the Edit reports.)
In examining the data I don't see any rhyme or reason why an unposted Sales Order invoice has a posting date or does not have one, but they surely do both. I cannot discern from the data what would trigger the posting date to appear. Further more what relevance does GL Posting Date have to an unposted invoice?
My response to the client: it is not clear to me why this was happening but it should not interfere with posting.
If anyone happens to have any information on this please share!
Client email came questioning the inconsistency of the GL Posting Date on Sales Order invoices in GP (2010.) Apparently some invoices have a posting date, others don't (as can be seen on the Edit reports.)
In examining the data I don't see any rhyme or reason why an unposted Sales Order invoice has a posting date or does not have one, but they surely do both. I cannot discern from the data what would trigger the posting date to appear. Further more what relevance does GL Posting Date have to an unposted invoice?
My response to the client: it is not clear to me why this was happening but it should not interfere with posting.
If anyone happens to have any information on this please share!
Wednesday, May 14, 2014
Dynamics GP IV to GL Reconciliation Pt 2 - IV Reports
Given the facts and client situation stated in Pt. 1 of this series we are looking for the following:
1. Tying out the Inventory (IV) sub-ledger to the General Ledger (GL) in GP; and
2. Looking only at relevant "stock" items that should have value.
In Pt. 1 I stated that the Reconcile to GL report in Financial does not work for this as there are almost 80 IV accounts in the balance sheet and many items that are not stock items.
I'm going to take a quick look at the report list in the IV sub-ledger to see if these can help. The following is a summary by IV Reports menu list.
Item - These are detailed reports and of almost no use to reconciling the GL. Any information required at a detail item level is likely best obtained from a Smartlist instead.
Serial/Lots - Nothing to see here. Move along!
Activity - Now we're talking. Let's drill into some of these below.
Analysis - None of these are related to any kind of balancing tasks. Pass.
History - For the most part Smartlists are a better tool for this. Cost Change looked like a good candidate but either they've never changed an item cost or this report is not working as it comes up blank. (No I haven't asked if they've made a cost change and I doubt they have.)
As an interesting side-note I went to the IV Utilities > Adjust Costs window and called up an item that was received at $0 cost (inadvertently) I received "You cannot post. The fiscal period that contains the posting date has been closed." We know the drill, right? Fiscal Periods - Open (this is a sand-box test instance by the way.) Error gone!
What I was hoping to accomplish in that screen is not what I expected. The item that went into IV with $0 cost and was entirely (and regrettably) sold before the invoice was matched displays with the adjusted invoice cost. On this screen I only see the adjusted, invoiced cost (which is correct.) (Leaves me with my $0 cost sold inventory transactions that didn't really cost $0.)
Another thing: Does anyone use these History reports? I can't make heads or tails of the Item Period History and the Sales Summary looks completely useless - better as a Smartlist.
Ok so let's take a look at some of the Activity reports and what they can do.
Purchase Receipts - Regrettably does not include a Class range restriction. Could perhaps be useful as a reference for discrepancy research but again a Smartlist would be more useful.
Stock Status - This report includes a "Segment" option so we can select the natural IV account from the GL. This report does include a "Total Inventory Value" so it might be useful however:
a. It shows Current Cost - will this be FIFO cost? Somehow I think not.
b. How is this report applying the Segment selection?
Further investigation warranted. In particular if this report can tie to the current GL.
Historical Stock Status - This could be good. Has Item Class as a Range. As side-note we upgraded them from GP 10 recently so the amounts on this report might be suspect according to this. Note you will need a Partner or Customer login to see that. The gist is the amounts in SEE30303 table might require an update and the Reset Tool will do this. Definitely looking at this one in more detail but from what I know this shows the roll-back of the counts from current on-hand quantities.
Historical IV Trial Balance - Real potential here but some problems. First this report is definitely account oriented, but again unclear on what the account refers to. It is possible to sort by account number, and you can specify an account number range or class. It shows account totals but here's where it goes wrong: When you try to match those totals up to GL periods it doesn't work. A clue is in the attempt to use the "Date" range to select. It apparently wants to start from the beginning of time. I could not find a way to restrict the report to a specific GL period to reconcile.
So some more work is warranted on the two Stock Status reports but I don't think the others will assist my reconciliation in any way.
1. Tying out the Inventory (IV) sub-ledger to the General Ledger (GL) in GP; and
2. Looking only at relevant "stock" items that should have value.
In Pt. 1 I stated that the Reconcile to GL report in Financial does not work for this as there are almost 80 IV accounts in the balance sheet and many items that are not stock items.
I'm going to take a quick look at the report list in the IV sub-ledger to see if these can help. The following is a summary by IV Reports menu list.
Item - These are detailed reports and of almost no use to reconciling the GL. Any information required at a detail item level is likely best obtained from a Smartlist instead.
Serial/Lots - Nothing to see here. Move along!
Activity - Now we're talking. Let's drill into some of these below.
Analysis - None of these are related to any kind of balancing tasks. Pass.
History - For the most part Smartlists are a better tool for this. Cost Change looked like a good candidate but either they've never changed an item cost or this report is not working as it comes up blank. (No I haven't asked if they've made a cost change and I doubt they have.)
As an interesting side-note I went to the IV Utilities > Adjust Costs window and called up an item that was received at $0 cost (inadvertently) I received "You cannot post. The fiscal period that contains the posting date has been closed." We know the drill, right? Fiscal Periods - Open (this is a sand-box test instance by the way.) Error gone!
What I was hoping to accomplish in that screen is not what I expected. The item that went into IV with $0 cost and was entirely (and regrettably) sold before the invoice was matched displays with the adjusted invoice cost. On this screen I only see the adjusted, invoiced cost (which is correct.) (Leaves me with my $0 cost sold inventory transactions that didn't really cost $0.)
Another thing: Does anyone use these History reports? I can't make heads or tails of the Item Period History and the Sales Summary looks completely useless - better as a Smartlist.
Ok so let's take a look at some of the Activity reports and what they can do.
Purchase Receipts - Regrettably does not include a Class range restriction. Could perhaps be useful as a reference for discrepancy research but again a Smartlist would be more useful.
Stock Status - This report includes a "Segment" option so we can select the natural IV account from the GL. This report does include a "Total Inventory Value" so it might be useful however:
a. It shows Current Cost - will this be FIFO cost? Somehow I think not.
b. How is this report applying the Segment selection?
Further investigation warranted. In particular if this report can tie to the current GL.
Historical Stock Status - This could be good. Has Item Class as a Range. As side-note we upgraded them from GP 10 recently so the amounts on this report might be suspect according to this. Note you will need a Partner or Customer login to see that. The gist is the amounts in SEE30303 table might require an update and the Reset Tool will do this. Definitely looking at this one in more detail but from what I know this shows the roll-back of the counts from current on-hand quantities.
Historical IV Trial Balance - Real potential here but some problems. First this report is definitely account oriented, but again unclear on what the account refers to. It is possible to sort by account number, and you can specify an account number range or class. It shows account totals but here's where it goes wrong: When you try to match those totals up to GL periods it doesn't work. A clue is in the attempt to use the "Date" range to select. It apparently wants to start from the beginning of time. I could not find a way to restrict the report to a specific GL period to reconcile.
So some more work is warranted on the two Stock Status reports but I don't think the others will assist my reconciliation in any way.
Tuesday, May 13, 2014
Number to Words
I need to create a SQL Server Report that looks like a check image from Dynamics SL. In order to accomplish this little feat one of the things that happens on a check is the check amount is presented in words!
This means I need a function that converts a check amount into words.
Thank you to Novick Software that published just such a function.
This means I need a function that converts a check amount into words.
Thank you to Novick Software that published just such a function.
Dynamics GP IV to GL reconciliation - Pt 1
Anyone ever try to reconcile the Dynamics GP Inventory (IV) sub-ledger to the General Ledger (GL)?
Oh I'm sure it's possible somewhere, just not at my client.
At this client they have a mix of items, some they track in IV some not. They ones they DO track as IV items, where counts and value matter, are configured so their default IV account posts the GL IV account range, call it 14x. Other items that do not (in reality not supposed to) carry a value are coded to post Cost of Goods Sold (COGS) accounts instead.
So problem number one is how to segregate the valued inventory from the no-value inventory? Well a quick review of the IV Class designation reveals they are not really using it. So we created a STOCK class and assigned it (via SQL programming magic) to all the items that have the 14x accounts assigned as their Inventory GL account.
Now we have a basis for selecting only stock inventory on the various GP reports available in IV.
Problem two is that they post one natural GL account for IV but many different sub-accounts. This is so they can track values by department. What this means is the value of their stock inventory is spread across almost 80 different accounts. The implication of this is that the "Reconcile to GL" function in Financial Routines is almost useless.
So now we have a means of selecting only stock inventory for reporting but have almost 80 GL account balances to compare that to. Sweet.
If you have any experience at all with GP IV you know there are few options available. Creating custom reports is perhaps the only viable solution. In future articles I will explore how to create reports using SQL queries from the GP database that will assist in this matter.
Just to dispense with particulars here is what they don't use: bins, serial items, lots. Features they do use are kits and sites.
Oh I'm sure it's possible somewhere, just not at my client.
At this client they have a mix of items, some they track in IV some not. They ones they DO track as IV items, where counts and value matter, are configured so their default IV account posts the GL IV account range, call it 14x. Other items that do not (in reality not supposed to) carry a value are coded to post Cost of Goods Sold (COGS) accounts instead.
So problem number one is how to segregate the valued inventory from the no-value inventory? Well a quick review of the IV Class designation reveals they are not really using it. So we created a STOCK class and assigned it (via SQL programming magic) to all the items that have the 14x accounts assigned as their Inventory GL account.
Now we have a basis for selecting only stock inventory on the various GP reports available in IV.
Problem two is that they post one natural GL account for IV but many different sub-accounts. This is so they can track values by department. What this means is the value of their stock inventory is spread across almost 80 different accounts. The implication of this is that the "Reconcile to GL" function in Financial Routines is almost useless.
So now we have a means of selecting only stock inventory for reporting but have almost 80 GL account balances to compare that to. Sweet.
If you have any experience at all with GP IV you know there are few options available. Creating custom reports is perhaps the only viable solution. In future articles I will explore how to create reports using SQL queries from the GP database that will assist in this matter.
Just to dispense with particulars here is what they don't use: bins, serial items, lots. Features they do use are kits and sites.
Network blocks
So yesterday my integration at client T starts throwing errors every 10 minutes just before 8 AM. This integration runs every 10 minutes all day long and reads a file of inventory data from a share on another server. The integration server is our analytics box and the other server is the accounting/GP server.
The first clue as to the problem source was when I logged into the analytics/integration server and could not connect to the accounting server either from SQL Management Studio or via RDP. I opened the Integration Services package that was the source of the errors and it was unable to contact the accounting server. I then tried our SQL Reports hosted on the analytics server and they threw an error as they could not draw data from accounting.
So with those facts in hand I posted IT for assistance. Obviously something had changed to restrict access between the servers.
Magically the problem was resolved by IT. Here is their response.
"Our servers are generally members of three VLANS and there was a conflict between multiple NICS's, teaming, and VLAN allocation."
The first clue as to the problem source was when I logged into the analytics/integration server and could not connect to the accounting server either from SQL Management Studio or via RDP. I opened the Integration Services package that was the source of the errors and it was unable to contact the accounting server. I then tried our SQL Reports hosted on the analytics server and they threw an error as they could not draw data from accounting.
So with those facts in hand I posted IT for assistance. Obviously something had changed to restrict access between the servers.
Magically the problem was resolved by IT. Here is their response.
"Our servers are generally members of three VLANS and there was a conflict between multiple NICS's, teaming, and VLAN allocation."
Thursday, May 8, 2014
Journal Entry Inquiry
Client asked me today about his attempt to look up a reversing JE in the Journal Entry Inquiry screen in GP 2013. When he provided the JE number the JE Inquiry screen showed the reversing entry. He was wondering what happened to the originating entry and why didn't it show?
Obviously this screen can only show one entry at a time. My guess is the lookup this window does returns whatever the first transaction it finds based on the indexes or whatever.
My answer is it's a simple matter of using the navigation buttons on the bottom left of the screen to go back or forward. Doing this both transactions are visible to the user.
I'd appreciate any comments if anyone knows of any other tricks or info related to this.
Update: Feedback from the client reveals that he was looking for a hard-copy report that showed both entries. I pointed him at the Cross-Reference Journal Entry report, which was exactly what he was looking for.
Obviously this screen can only show one entry at a time. My guess is the lookup this window does returns whatever the first transaction it finds based on the indexes or whatever.
My answer is it's a simple matter of using the navigation buttons on the bottom left of the screen to go back or forward. Doing this both transactions are visible to the user.
I'd appreciate any comments if anyone knows of any other tricks or info related to this.
Update: Feedback from the client reveals that he was looking for a hard-copy report that showed both entries. I pointed him at the Cross-Reference Journal Entry report, which was exactly what he was looking for.
SQL Integration Services error
Client T has an SSIS job running in SQL 2008 R2 that failed last night for the first time in a long time.
"What changed?"
Apparently they moved the source Access (accdb) database to a sub-folder on the network a level below where it was. Thanks for the notice!
Not so fast though. The first problem I have is I can no longer open the SSIS project that contains this database. Business Intelligence Development Studio (BIDS - the version of Visual Studio used by SQL Server) turns grey and goes unresponsive to where I have to log off the server to kill it. I can connect to other accdb files without issue in SSIS.
The next problem is when I try to create a new SSIS project in BIDS and connect to the source accdb I receive the following:
"Microsoft Visual Studio: Attempted to read or write protected memory. This is often an indication that other memory is corrupt." "other memory"? What other memory?
Searching for that error message leads to many hits; many meager attempts to resolve the error - but no solution.
I can open the accdb in both Excel and Access (2010.) Interestingly when I try to create a backup of it in Access I get:
"You attempted to open a database that is already opened by user 'Admin' on machine 'x'. Try again when the database is available."
I queried local IT to see if user 'Admin' can exit the database. No response as yet but I suspect this might be the issue. I will have to update this case later with the results as other items press.
"What changed?"
Apparently they moved the source Access (accdb) database to a sub-folder on the network a level below where it was. Thanks for the notice!
Not so fast though. The first problem I have is I can no longer open the SSIS project that contains this database. Business Intelligence Development Studio (BIDS - the version of Visual Studio used by SQL Server) turns grey and goes unresponsive to where I have to log off the server to kill it. I can connect to other accdb files without issue in SSIS.
The next problem is when I try to create a new SSIS project in BIDS and connect to the source accdb I receive the following:
"Microsoft Visual Studio: Attempted to read or write protected memory. This is often an indication that other memory is corrupt." "other memory"? What other memory?
Searching for that error message leads to many hits; many meager attempts to resolve the error - but no solution.
I can open the accdb in both Excel and Access (2010.) Interestingly when I try to create a backup of it in Access I get:
"You attempted to open a database that is already opened by user 'Admin' on machine 'x'. Try again when the database is available."
I queried local IT to see if user 'Admin' can exit the database. No response as yet but I suspect this might be the issue. I will have to update this case later with the results as other items press.