How to Retrieve All Vendor Location IDs in X++ (Beyond Primary Addresses) in Microsoft Dynamics 365 Finance & Operations

How to Retrieve All Vendor Location IDs in X++ (Beyond Primary Addresses) in Microsoft Dynamics 365 Finance & Operations

This method builds a custom lookup to fetch all location IDs linked to a vendor, regardless of whether they are marked as primary.


✅ Key Idea

Instead of relying on flags like IsPrimary, this approach:

  • Starts from LogisticsLocation (root)
  • Joins through party and vendor tables
  • Filters only by Vendor Account
  • Returns all related locations
💻 Code with Explanation

public void lookup()
{
    // Create lookup object for LogisticsLocation table
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(LogisticsLocation), this);

    // Initialize query
    Query query = new Query();

    // Declare data sources (tables)
    QueryBuildDataSource qbdsLogisticsLocation, qbdsDirParty, qbdsVendTable,
             qbdsTaxInfo, qbdsTaxReg;

    // ✅ ROOT DATASOURCE
    // Start from LogisticsLocation to ensure we fetch ALL locations
    qbdsLogisticsLocation = query.addDataSource(tableNum(LogisticsLocation));

    // 🔗 Join DirPartyPostalAddressView (connects location to party)
    qbdsDirParty = qbdsLogisticsLocation.addDataSource(tableNum(DirPartyPostalAddressView));
    qbdsDirParty.relations(false); // Disable auto relations
    qbdsDirParty.joinMode(JoinMode::InnerJoin);
    qbdsDirParty.addLink(
        fieldNum(LogisticsLocation, RecId), 
        fieldNum(DirPartyPostalAddressView, Location)
    );

    // 🔗 Join VendTable (connect party to vendor)
    qbdsVendTable = qbdsDirParty.addDataSource(tableNum(VendTable));
    qbdsVendTable.relations(false);
    qbdsVendTable.joinMode(JoinMode::InnerJoin);
    qbdsVendTable.addLink(
        fieldNum(DirPartyPostalAddressView, Party), 
        fieldNum(VendTable, Party)
    );

    // ✅ Filter by Vendor Account
    qbdsVendTable.addRange(fieldNum(VendTable, AccountNum))
        .value(queryValue(MvdVendorInvoiceHeader.VendAccount));

    // 🔗 Join TaxInformation_IN (location-based GST info)
    qbdsTaxInfo = qbdsDirParty.addDataSource(tableNum(TaxInformation_IN));
    qbdsTaxInfo.relations(false);
    qbdsTaxInfo.joinMode(JoinMode::InnerJoin);
    qbdsTaxInfo.addLink(
        fieldNum(DirPartyPostalAddressView, Location), 
        fieldNum(TaxInformation_IN, RegistrationLocation)
    );

    // 🔗 Join TaxRegistrationNumbers_IN (GST registration details)
    qbdsTaxReg = qbdsTaxInfo.addDataSource(tableNum(TaxRegistrationNumbers_IN));
    qbdsTaxReg.relations(false);
    qbdsTaxReg.joinMode(JoinMode::InnerJoin);
    qbdsTaxReg.addLink(
        fieldNum(TaxInformation_IN, GSTIN), 
        fieldNum(TaxRegistrationNumbers_IN, RecId)
    );

    // ✅ Optional filter: Registration Number
    if (MvdVendorInvoiceHeader.RegistrationNumber)
    {
        qbdsTaxReg.addRange(fieldNum(TaxRegistrationNumbers_IN, RegistrationNumber))
            .value(queryValue(MvdVendorInvoiceHeader.RegistrationNumber));
    }

    // 🔽 Sort results by LocationId
    qbdsLogisticsLocation.addSortField(
        fieldNum(LogisticsLocation, LocationId), 
        SortOrder::Ascending
    );

    // 📌 Define fields to display in lookup
    sysTableLookup.addLookupfield(fieldNum(LogisticsLocation, LocationId));
    sysTableLookup.addLookupfield(fieldNum(LogisticsLocation, Description));

    // 🔗 Assign query to lookup
    sysTableLookup.parmQuery(query);

    // 🚀 Execute lookup
    sysTableLookup.performFormLookup();
}

Comments

Post a Comment

Popular posts from this blog

Product Tax Rate Search Report (HSN & SAC) in D365 F&O

Image
  📊 Product Tax Rate Search Report (HSN & SAC) in D365 F&O 🔹 1. Introduction In  Microsoft Dynamics 365 Finance & Operations , tax configurations are maintained within complex Tax Engine runtime tables. Although these structures are highly powerful, they are not easily accessible or intuitive for business users. To overcome this challenge, the Product Tax Rate Search Report is designed to provide a simplified and dynamic approach for retrieving product-wise GST tax details using key business parameters. 🔹 2. Purpose of the Report This report enables users to fetch tax details based on: HSN Code (for Goods) SAC Code (for Services) Item ID Item Group Main Account (Posting GL) Tax Rate / Tax Rate Type 👉 The report dynamically retrieves  GST Tax Rate (IGST)  from the Tax Engine, ensuring  real-time and accurate tax configuration visibility . 🔹 3. Business Need In standard D365 F&O: Tax configurations are stored in  Tax Runtime tables These ...
Read more

Fetching Mandatory Default Dimensions from Account Structure in D365 F&O

  Fetching Mandatory Default Dimensions from Account Structure in D365 F&O In Dynamics 365 Finance & Operations (D365 F&O), Account Structures and Advanced Rules together determine which financial dimensions are required, optional, or restricted during transaction posting. In this context, I am attempting to retrieve the mandatory financial dimensions using X++.  1. Business Requirement When a user selects a Main Account , the system should: Identify the applicable Account Structure Evaluate Advanced Rules Return only mandatory financial dimensions 2. Conceptual Understanding 🔹 What Drives Mandatory Dimensions? Mandatory dimensions are derived from: Account Structure Advanced Rule Structure Constraint Nodes (IsOptional flag) X++ Implementation class MVDDimensionRuleCriteria {     public static container getMaxDimensionRule(MainAccountNum _mainaccount)     {         // Table declarations       ...
Read more