 Posted 4/13/2009 6:45:13 PM
|
|
|
|
Scenerio:
A supplier calls and gives you an Invoice number. You look it up and it shows that it was paid. However it does not show you the Check Number. What is the easiest way to locate the check number paid against a given invoice?
I created a SQL View that will display the information..but I am not a developer and do not use the SDK.. Is there any way through the user interface to add this view to a grid and be able to pull it up from a form or a menu? I was able to create a basic report but that takes too long when the supplier is on the phone.
You would think with the complexity of the CRM that this simple function..that seems a common place task..would already be in the system somewhere.
I have provided the SQL code below if anyone wants to create a simple plugin or grid. I think it would help a lot of end-users.
SQL:
SELECT SupplierPaymentDetailView.BillCode, SupplierPaymentDetailView.BillDate, SupplierBillTransactionHistoryView.InvoiceCode,
SupplierBillTransactionHistoryView.Reference, SupplierBillTransactionHistoryView.DatePaid, SupplierBillTransactionHistoryView.SupplierCode,
SupplierPaymentDetailView.AppliedAmount, SupplierPaymentDetailView.AmountPaid, SupplierPaymentDetailView.AppliedAmountRate,
SupplierPaymentDetailView.AmountPaidRate, SupplierPaymentDetailView.PayableCode, SupplierPaymentDetailView.Total
FROM SupplierPaymentDetailView INNER JOIN
SupplierBillTransactionHistoryView ON SupplierPaymentDetailView.BillCode = SupplierBillTransactionHistoryView.BillCode
Has anyone found a better solution?
Mark B Littman
Innovative ERP Solutions Inc.
Chief Information Officer (CIO)
9848 SW Glenbrook Dr.
Port Saint Lucie, FL 34987
Main Tel: 772-336-2831
mlittman@ierps.com
http://www.ierps.com
LinkedIn Profile
http://www.linkedin.com/in/markblittman
See who we know in common
|
|
|
Posted 4/14/2009 8:51:42 AM
|
|
|
|
Mark,
Our DS Data Explorer plugin will let you display any table, view, or even SQL statement result in a grid and live pivot table. But in your case, this will still be cumbersome.
How do you search for the vendor invoice? Are you looking through the supplier form at the transaction listing? Or looking up the supplier bill?
How would you like to see the information presented. What form should the new tab be on? Any additional drill-downs?
This sounds like a fairly simply plugin and we're always looking for ideas!
Thanks,
Bill Dimes
DimeSoft Business Solutions, Inc.
www.dimesoftinc.com
www.interprisesuiteplugins.com
|
|
|
Posted 4/14/2009 10:55:21 AM
|
|
|
|
| Mark, There is an easy way to locate the check number that paid a supplier invoice. 1) Locate and Open the Supplier Record.
2) Select the 'Transactions' Tab.
3) Select the 'Ledger' Tab.
4) Select the 'View Option' to 'All'.
5) Locate the Supplier Bill.
6) Double click on the 'Outstanding Amount' field. 
7) Once the allocation screen appears, review which Supplier Payments were allocated to the Supplier Bill.
 8) Locate the Supplier Payment. The check number will appear in the 'Reference' column.
 Hope this helps you out.
BJ Dimes
Software Development & Support Engineer
DimeSoft Business Solutions, Inc.
Interprise Suite Plugins
Interprise Suite Blog
|
|
|
Posted 4/17/2009 4:11:02 AM
|
|
|
|
Bill,
Although that was very helpful....the customer still felt it took way too long to look up the information. I ended creating a Custom View with the following Criteria:
SQL :
SELECT
SupplierPaymentDetailView.BillCode,
SupplierPaymentDetailView.BillDate,
SupplierBillTransactionHistoryView.InvoiceCode,
SupplierBillTransactionHistoryView.Reference,
SupplierBillTransactionHistoryView.SupplierCode,
SupplierPaymentDetailView.AppliedAmount,
SupplierPaymentDetailView.AmountPaid,
SupplierPaymentDetailView.AppliedAmountRate,
SupplierPaymentDetailView.AmountPaidRate,
SupplierPaymentDetailView.PayableCode,
SupplierPaymentDetailView.Total,
SupplierPaymentView.CheckNumber,
SupplierPaymentView.IsVoided,
SupplierPaymentView.DatePaid
FROM
SupplierPaymentDetailView
INNER JOIN SupplierBillTransactionHistoryView ON (SupplierPaymentDetailView.BillCode = SupplierBillTransactionHistoryView.BillCode)
INNER JOIN SupplierPaymentView ON (SupplierPaymentDetailView.PayableCode = SupplierPaymentView.PayableCode)
AND (SupplierPaymentDetailView.AllocationCode = SupplierPaymentView.AllocationCode)
WHERE
SupplierPaymentView.IsVoided = '0'
Then In the Data Dictionary I made the Supplier Code and The Invoice Code Dynamic Lists.
I created a quick simple report that asks for Supplier and Invoice Number and it kicks out an immediate list of Checks and details that were used to pay that invoice. The user does not have to print they can go to the criteria filter and use the grids to look up the info.
I will make the report and view available to all that may want it.
Mark B Littman
Innovative ERP Solutions Inc.
Chief Information Officer (CIO)
9848 SW Glenbrook Dr.
Port Saint Lucie, FL 34987
Main Tel: 772-336-2831
mlittman@ierps.com
http://www.ierps.com
LinkedIn Profile
http://www.linkedin.com/in/markblittman
See who we know in common
|
|
|
|
