X number of Y fields filled filter logic in a generic Inquiry

@BrendanL  This requirement is not straightforward to achieve using only a Generic Inquiry. However, we can create a SQL View to check the conditions and ensure that at least 3 fields are filled for each contact.

Below is a sample SQL View for your reference. Please modify this view with your specific fields and create a DAC and the corresponding Generic Inquiry for it.

CREATE VIEW ContactsWithMinFields AS
SELECT ContactID, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9
FROM Contact
WHERE 
    (CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field3 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field4 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field5 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field6 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field7 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field8 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field9 IS NOT NULL THEN 1 ELSE 0 END) >= 3

CREATE VIEW ContactsWithMinFields AS
SELECT ContactID, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9
FROM Contact
WHERE 
    (CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field3 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field4 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field5 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field6 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field7 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field8 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field9 IS NOT NULL THEN 1 ELSE 0 END) >= 3

@Naveen Boga so there is no way to make the GI reflect the requirements on its own? my manager would like this to be a live inquiry that can be checked easily so I am not sure this will meet his request.

I don’t think so, because we cannot get the count value to check greater than equal to 3 in GI - Conditions tab.

Hence suggested above approach, Create SQL View → Create DAC → Create GI.

Alright one last question before I get started on this, how will it function when I have the SQL view created and setup through the DAC-GI? will it look like a normal GI or will it require the same step to be repeated every time we want to gather this data? what can I expect from going down this route?

No, a SQL View needs to be created only once. After creating the DAC class and the Generic Inquiry (GI) from this DAC, it will function like a regular GI

@Naveen Boga Okay thank you, that should fulfill the requirement my manager put forward. I have never done this before but I will find some guides to walk me through the process as I learn it.

Once you done with the SQL View creation, just let me know I will help you to create DAC and GI.

@Naveen Boga Alright I have created the view (image attached.)
 

@Naveen Boga for step 9 could you show me what you mean by

“In this DAC class file, add property IsKey = true for the key fields.”

I just want to make sure I got this setup right.

Hi @BrendanL ,

If you are ok with using a filter, you might not need a customization project.

Although you can’t include the Condition directly in the GI, you should be able to use a GI formula similar to the logic in the SQL query that @Naveen Boga described to calculate the count as a column.  Be sure to specify a schema for the calculated column based on a qty field in Acumatica. 

After that you should be able to create a filter tab on the GI that shows the records with a value >=3.

Hope this helps!

Laura

Yes @BrendanL  For the Primary Key fields, we need to decorate the DAC fields with IsKey = true like below for your reference.

In the SOOrder DAC both (OrderType and OrderNbr) fields are Primary Key fields in the database, so in the DAC we need to mark as IsKey = true 

@Naveen Boga Does this look Correct?
 

Keep it for only ContactID and remove IsKey = true for all custom fields.

@Naveen Boga Okay I got that done, I assume I will need to publish this Customization Project in order to utilize the filter in the GI?

also if you would be so Kind as to just verify that this is setup right I will post my code here.

using System;
using PX.Data;

namespace NewDAC3VAL
{
  [Serializable]
  [PXCacheName("ContactsWithMinFields")]
  public class ContactsWithMinFields : IBqlTable
  {
    #region ContactID
    [PXDBIdentity(IsKey = true)]
    public virtual int? ContactID { get; set; }
    public abstract class contactID : PX.Data.BQL.BqlInt.Field<contactID> { }
    #endregion

    #region UsrSSProductsManufactured
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSProducts Manufactured")]
    public virtual string UsrSSProductsManufactured { get; set; }
    public abstract class usrSSProductsManufactured : PX.Data.BQL.BqlString.Field<usrSSProductsManufactured> { }
    #endregion

    #region UsrSSTop3PurchasingObjectives
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSTop3 Purchasing Objectives")]
    public virtual string UsrSSTop3PurchasingObjectives { get; set; }
    public abstract class usrSSTop3PurchasingObjectives : PX.Data.BQL.BqlString.Field<usrSSTop3PurchasingObjectives> { }
    #endregion

    #region UsrSSTop3Challenges
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSTop3 Challenges")]
    public virtual string UsrSSTop3Challenges { get; set; }
    public abstract class usrSSTop3Challenges : PX.Data.BQL.BqlString.Field<usrSSTop3Challenges> { }
    #endregion

    #region UsrSSSatisfactionGap
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSSatisfaction Gap")]
    public virtual string UsrSSSatisfactionGap { get; set; }
    public abstract class usrSSSatisfactionGap : PX.Data.BQL.BqlString.Field<usrSSSatisfactionGap> { }
    #endregion

    #region UsrSSProposedSolutionAdvantages
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSProposed Solution Advantages")]
    public virtual string UsrSSProposedSolutionAdvantages { get; set; }
    public abstract class usrSSProposedSolutionAdvantages : PX.Data.BQL.BqlString.Field<usrSSProposedSolutionAdvantages> { }
    #endregion

    #region UsrSSOwnerManagerFactor
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSOwner Manager Factor")]
    public virtual string UsrSSOwnerManagerFactor { get; set; }
    public abstract class usrSSOwnerManagerFactor : PX.Data.BQL.BqlString.Field<usrSSOwnerManagerFactor> { }
    #endregion

    #region UsrSSOtherDecisionInfluencers
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSOther Decision Influencers")]
    public virtual string UsrSSOtherDecisionInfluencers { get; set; }
    public abstract class usrSSOtherDecisionInfluencers : PX.Data.BQL.BqlString.Field<usrSSOtherDecisionInfluencers> { }
    #endregion

    #region UsrSSKeyCorporateInitiatives
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSKey Corporate Initiatives")]
    public virtual string UsrSSKeyCorporateInitiatives { get; set; }
    public abstract class usrSSKeyCorporateInitiatives : PX.Data.BQL.BqlString.Field<usrSSKeyCorporateInitiatives> { }
    #endregion

    #region UsrSSCurrentSuppliers
    [PXDBString(500, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Usr SSCurrent Suppliers")]
    public virtual string UsrSSCurrentSuppliers { get; set; }
    public abstract class usrSSCurrentSuppliers : PX.Data.BQL.BqlString.Field<usrSSCurrentSuppliers> { }
    #endregion
  }
}

Yes, please publish this customization project and create the GI and verify now.

Okay it is published and I added it to the GI in the Tables (shown in image) is there another step that must be completed?
 

@BrendanL  Are you adding this DAC to the existing GI or new GI?

@Naveen Boga I had already created a separate GI when I was originally tasked with doing this, it is a copy of an existing GI that I also had created.

Oh okay. Are you able to fetch the expected data?

@Naveen Boga it seems to be working exactly the same as before, even contacts with no fields filled are showing up.

@Naveen Boga Okay so the Issue is that there seems to be no way to relate the NewDAC3VAL.ContactsWithMinFields table to the Contacts Table. how would I go about getting those tables to have the ability to join together?

@Naveen Boga okay so if I create an inquiry only using the new DAC table and create it that works to make it only pull the information desired but it does not work with trying to join other tables to it. I joined it manually to the Contacts table and if I pull the columns from the new DAC all the results are empty but if I pull from the contacts it prints all results regardless of the number filled.

@Naveen Boga Never mind I was able to figure out the solution, I just needed to rebuild the GI using the new DAC as the Parent Table for the start and working from there.