Method Community

In building out a Statement of Account report to Customers based on the Transaction table, I have encountered an issue trying to link Entity BillAddr fields to the Transaction table.  The Transaction table accepts links to the Entity BillAddress columns.  When I try to export the Transaction table, the linked columns appear in the available columns grid (with boxes checked) but when I hit the Export button I get a error message that the Linked Columns do not exist in the table.

Integration Tools menu>> Import / Export menu 

ERROR:Failure. The following fields do not exist in the table Transaction: 'AccountFullName', 'BillAddressAddr1', 'BillAddressAddr2', 'BillAddressAddr3', 'BillAddressCity', 'BillAddressPostalCode', 'BillAddressState', 'Contact'.

When I go into the report designer and open reports that are based on the Transaction table I g  et this error:

The report failed to be created

---------------------------
<ERROR><MESSAGE>Invalid column name 'AccountFullName'.
Invalid column name 'BillAddressAddr1'.
Invalid column name 'BillAddressAddr2'.
Invalid column name 'BillAddressAddr3'.
Invalid column name 'BillAddressCity'.
Invalid column name 'BillAddressPostalCode'.
Invalid column name 'BillAddressState'.
Invalid column name 'Contact'.</MESSAGE><STACKTRACE> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()...........(snip)

What gives???  Thanks.

Here are the screenshots:  http://imgur.com/e7VS5aG     http://imgur.com/L52Ccvf     http://imgur.com/8mbzHfe

Hello Robert,

    We had spoken off the forum posts and look at the problem.  There is a restriction that does not allow fields to be added to the transaction table. This is why you were getting the errors.  The answer will have to be going from the customer table or a child-transaction table.

    Your goal was to create a Statement of Account for a customer showing overdue statements in increments of 30 days (30, 60 90 etc.).   You can try to add other tables as sub-tables to reports.  Your report can be based on the Customer table and have the transaction type tables, ie. invoice, estimate, sales order, etc. as sub-tables.  Then you would have the information you need to generate the table.  I have not tried it, but when creating the report, you can choose a master table and then select Detail tables below on the Create new report screen.

Greg

I did as suggested and ran into new Report Design (RD) issue.  I rebuilt my Statement of Account (SOA) report using Entity as the primary (parent) table with a related Transaction table (sub detail table, Transaction.EntityRef).  I could have used Customer but the results are simliar.  I have the Entity data printing but I need filters on BOTH Entity AND Transaction.  RD does not allow for filters on both the parent and child tables at the same time.  In the Screen button.  I allow for an optional Customer resultReportFilter to be selected and added.  If they don't select al Customer, all Customers with Balance > 0 are in the report. 

In trying add a RD Filter string, if I set the Data Member to Entity.Transaction.Entity, I finally get all the Transactions, unfiltered unfortunately, for the selected Entity (a Customer), but then the address script of the Entity (copied from another stock Method screen) goes empty as its based on the Master table.  If I set the Data Member back to Entity, I get the Address script back but only get the first, unfiltered, transaction row for the Entity.  

I need filters on both the Entity and Transaction tables in the report. 

I am very frustrated with the Report Designer.  Are there any complete, in-depth advanced training documents or videos for Report Designer?   I feel like I am using something from CPM days...

For an SOA report, the Transaction table needs to filter by:   TxnType in ('Invoice','ReceivePayment','CreditMemo') and AmountForAccountBalance <> 0

I have the current, 30, 60, 90, >90 bucket calculations working on the Transaction rows with this filter.  

I also have a filter on the Entity table,.  I need to filter on EntityType = 'Customer' and Balance > 0.  I optionally allow users to select a specific Customer as well (FullName = 'ACME Scientific').  If then don't pick a Customer, the report generates for all customers with balances.  When I was building my orginal SOA, I was housing the Transaction table so the Customer specific filter was Entity = 'ACME Scientific'.  But I can't use Transaction as the main table because I can't add linked the Customer Address columns to it. :-(

I had the Aging (30-60-90) report working based on Transaction except I could not get the Customer's address onto the report.  Is there a way to do database reads from a BeforePrint() function so I could pickup the BillAddress columns for each Customer on the fly??

Thanks.

On my issue trying to use Report Designer (RD) to create a new Statement Of Account, I realized RD does not have enough of a feature set to link Transaction and Customer AND handle filters on BOTH tables.  Instead, I decided it would be easier to empty the Invoice.Memo field and use the QuickBook statement of account report.  I created a method screen with a method button that did a table update on Invoice.Memo and set it equal to "empty" if it was not already "empty"  It took 1 minute to build the screen and 5 minutes to update Invoice.Memo on all our invoices.  It caused the MIE to clobber the QB machine MIE is running on.  I expect once Method syncs the changes up to QB, all will be well again.  Some of the QB statement reports are starting to show without the memo data so its just a matter of sync time before we can mail out our statements from QB.  Problem solved.