Solved

How to export fixed length field from Generic Inquiry

  • 7 February 2022
  • 9 replies
  • 285 views
Joe Schmucker
Rank
Userlevel 6
Badge +3

I have a generic inquiry that pulls a listing of checks.  I want to export a file using that GI.  However, the bank wants the file to have fixed length fields with NO DELIMITER.  The GI exports the fields using the CSV Data Provider.  The GI can be used to pad fields for the length with formulas, but I don’t want commas in the record.

It is important to note that this file is NOT part of the payment processing.  It is a post hoc report that is used to provide to the bank as a “Safe Pay” listing.  The bank uses this file to know that they are not to cash any checks unless they have been provided on the Safe Pay file.  

Sergey provided a really nice example of how to write a custom Data Provider https://asiablog.acumatica.com/2016/09/custom-integration-services-data.html

However, it is using CSV as a delimiter.  It seems like every option I can find online involves using a delimiter.

One thought I had as a possible solution is to use a SQL view to bring back the data fields in a SINGLE record per line.  I could use SQL to create the string for each record so each record has the fields in one row in whatever format I want.

The problem is that I don’t think I can pass parameters to a SQL view from Acumatica.

I could write a program to do this with a custom screen but I want to be able to use the built in functionality of Acumatica such as scheduling and using the GI to pull the data.

In the GI screen, I thought about putting all the outputs into a single field.  However, each field needs to be on it’s own line.  If I could put all the fields onto the first row of the Results Grid and use a horribly complex formula, that might work but I don’t think that is possible.

Any suggestions?

 

Thanks!

Joe Schmucker

icon

Best answer by Naveen Boga 8 February 2022, 19:16

View original
Joe Schmucker, CPA

9 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @joe21  If you don’t want to go with a custom screen and are familiar with SQL, go-ahead and create a SQL view. Please find the steps below. Hope this helps!

  • Create a SQL View and add to the customization package in Database scripts
  • For this SQL View, generate a DAC file.
  • You can this generated DAC file for creating a GI along with parameters as well.  
  • System will be treated as a normal GI so that you can use it for scheduling and using the GI to pull the data
Naveen B
Joe Schmucker
Rank
Userlevel 6
Badge +3

Baby steps I guess...

I created my SQL View and it returns the results the way I want.  This isn’t really a “table” and I think in order to make a DAC from it, does it need to be a “table” rather than a Select statement?

I created a simple DAC with one unbound field since it is not really a table:

    [Serializable]
    [PXCacheName("ICSSafePay View")]
    public class LSISafePayView : IBqlTable
    {
        #region ResultLine
        [PXString()]
        [PXUIField(DisplayName = "Result Line")]
        public virtual string ResultLine { get; set; }
        public abstract class resultLine : PX.Data.BQL.BqlString.Field<resultLine> { }
        #endregion
    }
 

Here is the SQL View

ALTER VIEW [dbo].[LSISafePay]
AS
    SELECT
        dbo.CATran.ExtRefNbr, 
        dbo.CATran.OrigTranType, 
        dbo.CATran.TranAmt,
        dbo.APPayment.AdjDate, 
        dbo.APPayment.DocType, 
        dbo.CATran.TranID,
        dbo.BAccount.AcctName,
        dbo.APRegister.Status,
        '123456789012345' + 
        RIGHT('0000000000' + ISNULL(dbo.CATran.ExtRefNbr, ''), 10) +
        RIGHT('000000000000' + RTRIM(LTRIM(CAST(CASE WHEN dbo.CATran.TranAmt < 0 THEN dbo.CATran.TranAmt * -100 
                                        ELSE dbo.CATran.TranAmt * 100 END AS INT))), 12) + 
        CAST(YEAR(dbo.APPayment.AdjDate) AS CHAR(4)) + RIGHT('0' + 
            RTRIM(LTRIM(CAST(MONTH(dbo.APPayment.AdjDate) AS CHAR(2)))), 2) + 
            RIGHT('0' + RTRIM(LTRIM(CAST(DAY(dbo.APPayment.AdjDate) AS CHAR(2)))), 2) +
            CASE WHEN dbo.APRegister.Status = 'V' THEN 'V' ELSE ' ' END +
        ' ' +
        LEFT(dbo.BAccount.AcctName + SPACE(100), 100) AS ResultLine
    FROM
        dbo.APPayment 
        INNER JOIN
        dbo.CATran 
            ON dbo.APPayment.CompanyID = dbo.CATran.CompanyID 
            AND dbo.APPayment.DocType = dbo.CATran.OrigTranType 
            AND dbo.APPayment.RefNbr = dbo.CATran.OrigRefNbr
        INNER JOIN
        dbo.APInvoice
            ON dbo.APInvoice.RefNbr = dbo.APPayment.RefNbr
        INNER JOIN
        dbo.APRegister 
            ON dbo.APRegister.DocType = APPayment.DocType
            AND dbo.APRegister.RefNbr = APPayment.RefNbr
        INNER JOIN
        dbo.BAccount 
            ON dbo.BAccount.BAccountID = dbo.APRegister.VendorID
    WHERE
        dbo.APPayment.DocType = 'CHK'
        AND (dbo.CATran.OrigTranType = 'CHK'
            OR dbo.CATran.OrigTranType = 'VCK'
            OR dbo.CATran.OrigTranType = 'VQC')
        AND dbo.APRegister.Status IN ('C', 'V')

GO

The results from selecting * from the view is exactly what I want to return.  I included the source fields in case that is what the GI would use for parameters:

I created a Generic Inquiry and pulled the ResultLine field from the View.  When I click VIEW INQUIRY, I get this error:

I pretty much expected it would not work but this is as far as I got.  

Unfortunately, I need further guidance.

Thanks,

Joe

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @joe21  I just checked with your SQL view and then created a DAC and then GI. For GI opened with out any error. Please find the screenshot below.

 

Hope you have added the IsKey= true for the DAC fields from (dbo.APPayment ,dbo.CATran ,  dbo.APInvoice,  dbo.APRegister,dbo.BAccount) 

 

You should add key fields in your SQL view select statement and in the DAC fields provide IsKey = true.

 

 

Naveen B
Joe Schmucker
Rank
Userlevel 6
Badge +3

Would you be able to paste the DAC you created?  I am not finding a way to auto generate it.  I will work on the ISKey stuff.

I don’t know why I am Jr Varsity II.  I should get demoted when I ask dumb questions.  :-)

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi Joe, here is the way of auto-generating the DAC from the customization package.

 

 

Naveen B
Joe Schmucker
Rank
Userlevel 6
Badge +3

Thank you Naveen.  I was trying to do that under the Data Access menu.  GOT IT!

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi Joe, 

I have done the few modifications to your SQL view by adding KEY fields and CompanyID conditions.

When you have multiple tenants, it will lead to the incorrect results.

Please find the latest SQL View and DAC file for your reference.

 

using System;
using PX.Data;

namespace Joe
{
  [Serializable]
  [PXCacheName("LSISafePay")]
  public class LSISafePay : IBqlTable
  {
    #region DocType
    [PXDBString(3, IsFixed = true,IsKey = true, InputMask = "")]
    [PXUIField(DisplayName = "Doc Type")]
    public virtual string DocType { get; set; }
    public abstract class docType : PX.Data.BQL.BqlString.Field<docType> { }
    #endregion

    #region RefNbr
    [PXDBString(15, IsUnicode = true,IsKey = true, InputMask = "")]
    [PXUIField(DisplayName = "Ref Nbr")]
    public virtual string RefNbr { get; set; }
    public abstract class refNbr : PX.Data.BQL.BqlString.Field<refNbr> { }
    #endregion

    #region CashAccountID
    [PXDBInt(IsKey = true)]
    [PXUIField(DisplayName = "Cash Account ID")]
    public virtual int? CashAccountID { get; set; }
    public abstract class cashAccountID : PX.Data.BQL.BqlInt.Field<cashAccountID> { }
    #endregion

    #region Tranid
    [PXDBLong(IsKey = true)]
    [PXUIField(DisplayName = "Tranid")]
    public virtual long? Tranid { get; set; }
    public abstract class tranid : PX.Data.BQL.BqlLong.Field<tranid> { }
    #endregion

    #region BAccountID
    [PXDBInt(IsKey = true)]
    [PXUIField(DisplayName = "BAccount ID")]
    public virtual int? BAccountID { get; set; }
    public abstract class bAccountID : PX.Data.BQL.BqlInt.Field<bAccountID> { }
    #endregion

    #region ExtRefNbr
    [PXDBString(40, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Ext Ref Nbr")]
    public virtual string ExtRefNbr { get; set; }
    public abstract class extRefNbr : PX.Data.BQL.BqlString.Field<extRefNbr> { }
    #endregion

    #region OrigTranType
    [PXDBString(3, IsFixed = true, InputMask = "")]
    [PXUIField(DisplayName = "Orig Tran Type")]
    public virtual string OrigTranType { get; set; }
    public abstract class origTranType : PX.Data.BQL.BqlString.Field<origTranType> { }
    #endregion

    #region TranAmt
    [PXDBDecimal()]
    [PXUIField(DisplayName = "Tran Amt")]
    public virtual Decimal? TranAmt { get; set; }
    public abstract class tranAmt : PX.Data.BQL.BqlDecimal.Field<tranAmt> { }
    #endregion

    #region AdjDate
    [PXDBDate()]
    [PXUIField(DisplayName = "Adj Date")]
    public virtual DateTime? AdjDate { get; set; }
    public abstract class adjDate : PX.Data.BQL.BqlDateTime.Field<adjDate> { }
    #endregion

    #region AcctName
    [PXDBString(255, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Acct Name")]
    public virtual string AcctName { get; set; }
    public abstract class acctName : PX.Data.BQL.BqlString.Field<acctName> { }
    #endregion

    #region Status
    [PXDBString(1, IsFixed = true, InputMask = "")]
    [PXUIField(DisplayName = "Status")]
    public virtual string Status { get; set; }
    public abstract class status : PX.Data.BQL.BqlString.Field<status> { }
    #endregion

    #region ResultLine
    [PXDBString(147, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Result Line")]
    public virtual string ResultLine { get; set; }
    public abstract class resultLine : PX.Data.BQL.BqlString.Field<resultLine> { }
    #endregion
  }
}

 

 

ALTER VIEW [dbo].[LSISafePay]
AS
    SELECT
	  dbo.APPayment.CompanyID,
	  dbo.APPayment.DocType,
	  dbo.APPayment.RefNbr,
	  dbo.CATran.CashAccountID,
	  dbo.CATran.TranID,
	  dbo.BAccount.BAccountID,

        dbo.CATran.ExtRefNbr, 
        dbo.CATran.OrigTranType, 
        dbo.CATran.TranAmt,
        dbo.APPayment.AdjDate,  
        dbo.BAccount.AcctName,
        dbo.APRegister.Status,
        '123456789012345' + 
        RIGHT('0000000000' + ISNULL(dbo.CATran.ExtRefNbr, ''), 10) +
        RIGHT('000000000000' + RTRIM(LTRIM(CAST(CASE WHEN dbo.CATran.TranAmt < 0 THEN dbo.CATran.TranAmt * -100 
                                        ELSE dbo.CATran.TranAmt * 100 END AS INT))), 12) + 
        CAST(YEAR(dbo.APPayment.AdjDate) AS CHAR(4)) + RIGHT('0' + 
            RTRIM(LTRIM(CAST(MONTH(dbo.APPayment.AdjDate) AS CHAR(2)))), 2) + 
            RIGHT('0' + RTRIM(LTRIM(CAST(DAY(dbo.APPayment.AdjDate) AS CHAR(2)))), 2) +
            CASE WHEN dbo.APRegister.Status = 'V' THEN 'V' ELSE ' ' END +
        ' ' +
        LEFT(dbo.BAccount.AcctName + SPACE(100), 100) AS ResultLine
    FROM
        dbo.APPayment 
        INNER JOIN
        dbo.CATran 
            ON dbo.APPayment.CompanyID = dbo.CATran.CompanyID 
            AND dbo.APPayment.DocType = dbo.CATran.OrigTranType 
            AND dbo.APPayment.RefNbr = dbo.CATran.OrigRefNbr
        INNER JOIN
        dbo.APInvoice
			ON dbo.APInvoice.CompanyID = dbo.APPayment.CompanyID
            And dbo.APInvoice.RefNbr = dbo.APPayment.RefNbr
        INNER JOIN
        dbo.APRegister 
            ON  dbo.APRegister.CompanyID = APPayment.CompanyID
			And dbo.APRegister.DocType = APPayment.DocType
            AND dbo.APRegister.RefNbr = APPayment.RefNbr
        INNER JOIN
        dbo.BAccount 
            ON  dbo.BAccount.CompanyID = dbo.APRegister.CompanyID
			And dbo.BAccount.BAccountID = dbo.APRegister.VendorID
    WHERE
        dbo.APPayment.DocType = 'CHK'
        AND (dbo.CATran.OrigTranType = 'CHK'
            OR dbo.CATran.OrigTranType = 'VCK'
            OR dbo.CATran.OrigTranType = 'VQC')
        AND dbo.APRegister.Status IN ('C', 'V')

GO

 

Naveen B
Joe Schmucker
Rank
Userlevel 6
Badge +3

Thank you @Naveen B.  I just finalized the project and sent it to the customer.  I couldn’t have done it without you.  This project has taught me a lot and folks like you are invaluable to lone rangers like me.

If anyone else ever needs a flat file export with one field of text per row, I can help...

Best regards, 
Joe S

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +17

Most Welcome @joe21 🙂

Naveen B

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved