BQL Join with a custom table giving Incorrect Syntax near '[keyword]' error at run time

Question

I'm trying to create a custom screen for scheduling the deliveries of the shipments in Acumatica. I created a custom table and DAC called DeliverySchedule for this purpose. And I create a data view that joins SOShipLine table with this new custom table. But when I load the screen I get a runtime error saying Incorrect Syntax near '='.

Error does not occur when I just join two Acumatica tables (SOShipment and SOShipLine). It happens when I join my custom table into the mix. I tried using the Request Profiler screen in Acumatica to debug but I do not have much information from that (or probably I do not know how to use it and read the data it provides)

public PXSelectJoin<SOShipLine, InnerJoin<SOShipment, On<SOShipment.shipmentNbr, Equal<SOShipLine.shipmentNbr>>, LeftJoin<DeliverySchedule, On<DeliverySchedule.shipmentNbr, Equal<SOShipLine.shipmentNbr>, And<DeliverySchedule.lineNbr, Equal<SOShipLine.lineNbr>>>>>, Where<SOShipment.status, Equal<SOShipmentStatus.open>>> Shipments;

DAC


    [Serializable]
    public class DeliverySchedule : IBqlTable
    {
        #region ShipmentNbr
        [PXString(15, IsKey = true)]
        [PXDBDefault(typeof(SOShipLine.shipmentNbr))]
        [PXParent(typeof(Select<SOShipLine, Where<SOShipLine.shipmentNbr, Equal<Current<DeliverySchedule.shipmentNbr>>, And<SOShipLine.lineNbr, Equal<Current<DeliverySchedule.lineNbr>>>>>))]
        [PXUIField(DisplayName = "Shipment No.")]
        public string ShipmentNbr { get; set; }
        public class shipmentNbr : IBqlField { }
        #endregion

        #region LineNbr
        [PXDBInt(IsKey = true)]   
        [PXDBDefault(typeof(SOShipLine.lineNbr))]
        [PXParent(typeof(Select<SOShipLine, Where<SOShipLine.shipmentNbr, Equal<Current<DeliverySchedule.shipmentNbr>>, And<SOShipLine.lineNbr, Equal<Current<DeliverySchedule.lineNbr>>>>>))]
        [PXUIField(DisplayName = "Line Nbr.", Visible = false)]
        public Int32? LineNbr { get; set; }
        public class lineNbr : IBqlField { }
        #endregion

        #region InventoryID
        [PXDBInt]
        [PXUIField(DisplayName = "Inventory ID")]
        public Int32? InventoryID { get; set; }
        public class inventoryID : IBqlField { }
        #endregion

        #region TransportID
        [PXDBInt]
        [PXUIField(DisplayName = "Transport")]
        public Int32? TransportID { get; set; }
        public class transportID : IBqlField { }
        #endregion

        #region DriverID
        [PXDBInt]
        [PXUIField(DisplayName = "Driver/ Captain")]
        public Int32? DriverID { get; set; }
        public class driverID : IBqlField { }
        #endregion

        #region TripNbr
        [PXDBString(15)]
        [PXUIField(DisplayName = "Trip No.")]
        public string TripNbr { get; set; }
        public class tripNbr : IBqlField { }
        #endregion

        #region ScheduledDelivery
        [PXDBDateAndTime(DisplayNameDate = "Scheduled Delivery Date", DisplayNameTime = "Scheduled Delivery Time")]
        public DateTime? ScheduledDelivery { get; set; }
        public class scheduledDelivery : IBqlField { }
        #endregion

        #region CustInvLvlBeforeDel
        [PXDBDecimal]
        [PXUIField(DisplayName = "Cust. Inv. Lvl. (Before Delivery)")]
        public decimal? CustInvLvlBeforeDel { get; set; }

        public class custInvLvlBeforeDel : IBqlField { }
        #endregion

        #region CustInvLvlAfterDel
        [PXDBDecimal]
        [PXUIField(DisplayName = "Cust. Inv. Lvl. (After Delivery)")]
        public decimal? CustInvLvlAfterDel { get; set; }

        public class custInvLvlAfterDel : IBqlField { }
        #endregion
    }

Trace Logs

Info. from the Tables column shown in the screen shot pasted below as text.

Tables
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
Favorite
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
"[WikiPage].[PageID], [WikiPage].[WikiID], [WikiPage].[ArticleType], [WikiPage].[ParentUID], [WikiPage].[Number], [WikiPage].[Name], [WikiPage].[Versioned], [WikiPage].[Folder], [WikiPage].[NoteID], NULL, NULL, NULL, [WikiPage].[CreatedByID], [WikiPage].[CreatedDateTime], [WikiPage].[LastModifiedByID], [WikiPage].[LastModifiedDateTime], [WikiPage].[tstamp], [WikiPage].[StatusID], [WikiPage].[ApprovalGroupID], [WikiPage].[ApprovalUserID], [WikiPage].[Width], [WikiPage].[Height], [WikiPage].[IsHtml]
FROM [WikiPage] [WikiPage]
WHERE [WikiPage].CompanyID IN (1, 2) AND 8 = SUBSTRING([WikiPage].CompanyMask, 1, 1) & 8 AND [WikiPage].[Name] = @P0
ORDER BY [WikiPage].[PageID] OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */"
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
WatchDog
SMPerformanceSettings
@@DBTS, IDENT_CURRENT('WatchDog') OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */
"TOP (201) [SOShipLine].[ShipmentNbr], [SOShipLine].[ShipmentType], [SOShipLine].[LineNbr], [SOShipLine].[SortOrder], [SOShipLine].[CustomerID], [SOShipLine].[ShipDate], [SOShipLine].[Confirmed], [SOShipLine].[Released], [SOShipLine].[LineType], [SOShipLine].[OrigOrderType], [SOShipLine].[OrigOrderNbr], [SOShipLine].[OrigLineNbr], [SOShipLine].[OrigSplitLineNbr], [SOShipLine].[Operation], [SOShipLine].[OrigPlanType], [SOShipLine].[InvtMult], [SOShipLine].[InventoryID], [SOShipLine].[PlanType], [SOShipLine].[SubItemID], [SOShipLine].[SiteID], [SOShipLine].[LocationID], [SOShipLine].[LotSerialNbr], [SOShipLine].[ExpireDate], [SOShipLine].[OrderUOM], [SOShipLine].[UOM], [SOShipLine].[ShippedQty], [SOShipLine].[BaseShippedQty], [SOShipLine].[BaseOriginalShippedQty], [SOShipLine].[UnassignedQty], [SOShipLine].[CompleteQtyMin], [SOShipLine].[BaseOrigOrderQty], [SOShipLine].[OrigOrderQty], [SOShipLine].[UnitCost], [SOShipLine].[ExtCost], [SOShipLine].[UnitPrice], [SOShipLine].[DiscPct], [SOShipLine].[AlternateID], [SOShipLine].[TranDesc], [SOShipLine].[UnitWeigth], [SOShipLine].[UnitVolume], [SOShipLine].[ExtWeight], [SOShipLine].[ExtVolume], [SOShipLine].[ProjectID], [SOShipLine].[TaskID], [SOShipLine].[ReasonCode], [SOShipLine].[IsFree], [SOShipLine].[ManualPrice], [SOShipLine].[ManualDisc], [SOShipLine].[IsUnassigned], [SOShipLine].[DiscountID], [SOShipLine].[DiscountSequenceID], [SOShipLine].[DetDiscIDC1], [SOShipLine].[DetDiscSeqIDC1], [SOShipLine].[DetDiscIDC2], [SOShipLine].[DetDiscSeqIDC2], [SOShipLine].[DetDiscApp], [SOShipLine].[DocDiscIDC1], [SOShipLine].[DocDiscSeqIDC1], [SOShipLine].[DocDiscIDC2], [SOShipLine].[DocDiscSeqIDC2], [SOShipLine].[ShipComplete], [SOShipLine].[RequireINUpdate], [SOShipLine].[NoteID], (SELECT TOP (1) [Note_s65].[NoteText] FROM [dbo].[Note] [Note_s65] WHERE [Note_s65].CompanyID IN (1, 2) AND 8 = SUBSTRING([Note_s65].CompanyMask, 1, 1) & 8 AND [Note_s65].[NoteId] = [SOShipLine].[NoteID]), (SELECT TOP (1) COUNT(*) FROM [dbo].[NoteDoc] [NoteDoc_s67] WHERE [NoteDoc_s67].CompanyID IN (1, 2) AND 8 = SUBSTRING([NoteDoc_s67].CompanyMask, 1, 1) & 8 AND [NoteDoc_s67].[NoteId] = [SOShipLine].[NoteID]), NULL, [SOShipLine].[CreatedByID], [SOShipLine].[CreatedByScreenID], [SOShipLine].[CreatedDateTime], [SOShipLine].[LastModifiedByID], [SOShipLine].[LastModifiedByScreenID], [SOShipLine].[LastModifiedDateTime], [SOShipLine].[tstamp], [SOShipLine].[UsrAPIGravity], [SOShipLine].[UsrMeterID], [SOShipLine].[UsrGrossQty], [SOShipLine].[UsrTemperature], [SOShipLine].[UsrToLocationID], [SOShipLine].[UsrBOLQty], [SOShipLine].[UsrBeginningTotalizer], [SOShipLine].[UsrEndingTotalizer], [SOShipment].[ShipmentNbr], [SOShipment].[ShipDate], [SOShipment].[Operation], [SOShipment].[ShipmentType], [SOShipment].[CustomerID], [SOShipment].[CustomerLocationID], [SOShipment].[SiteID], [SOShipment].[DestinationSiteID], [SOShipment].[ShipAddressID], [SOShipment].[ShipContactID], [SOShipment].[FOBPoint], [SOShipment].[ShipVia], [SOShipment].[UseCustomerAccount], [SOShipment].[Resedential], [SOShipment].[SaturdayDelivery], [SOShipment].[Insurance], [SOShipment].[GroundCollect], [SOShipment].[LabelsPrinted], [SOShipment].[PickListPrinted], [SOShipment].[ShippedViaCarrier], [SOShipment].[ShipTermsID], [SOShipment].[ShipZoneID], [SOShipment].[LineTotal], [SOShipment].[CuryID], [SOShipment].[CuryInfoID], [SOShipment].[CuryFreightCost], [SOShipment].[FreightCost], [SOShipment].[CuryFreightAmt], [SOShipment].[FreightAmt], [SOShipment].[CuryPremiumFreightAmt], [SOShipment].[PremiumFreightAmt], [SOShipment].[CuryTotalFreightAmt], [SOShipment].[TotalFreightAmt], [SOShipment].[TaxCategoryID], [SOShipment].[NoteID], NULL, NULL, NULL, [SOShipment].[Hold], [SOShipment].[Confirmed], [SOShipment].[Released], [SOShipment].[Status], [SOShipment].[LineCntr], [SOShipment].[BilledOrderCntr], [SOShipment].[UnbilledOrderCntr], [SOShipment].[ReleasedOrderCntr], [SOShipment].[ControlQty], [SOShipment].[ShipmentQty], [SOShipment].[ShipmentWeight], [SOShipment].[ShipmentVolume], [SOShipment].[PackageLineCntr], [SOShipment].[PackageWeight], [SOShipment].[IsPackageValid], [SOShipment].[CreatedByID], [SOShipment].[CreatedByScreenID], [SOShipment].[CreatedDateTime], [SOShipment].[LastModifiedByID], [SOShipment].[LastModifiedByScreenID], [SOShipment].[LastModifiedDateTime], [SOShipment].[tstamp], [SOShipment].[WorkgroupID], [SOShipment].[OwnerID], [SOShipment].[FreeItemQtyTot], CASE WHEN  ( [SOShipment].[Status] IS NULL) THEN  'A' ELSE  [SOShipment].[Status] END, [SOShipment].[UsrBOLNbr], [DeliverySchedule].[LineNbr], [DeliverySchedule].[InventoryID], [DeliverySchedule].[TransportID], [DeliverySchedule].[DriverID], [DeliverySchedule].[TripNbr], [DeliverySchedule].[CustInvLvlBeforeDel], [DeliverySchedule].[CustInvLvlAfterDel]
FROM [SOShipLine] [SOShipLine]
INNER JOIN [SOShipment] [SOShipment] ON [SOShipment].CompanyID = 2 AND [SOShipment].[ShipmentNbr] = [SOShipLine].[ShipmentNbr]
LEFT JOIN [DeliverySchedule] [DeliverySchedule] ON [DeliverySchedule].CompanyID = 2 AND = [SOShipLine].[ShipmentNbr] AND [DeliverySchedule].[LineNbr] = [SOShipLine].[LineNbr]
WHERE [SOShipLine].CompanyID = 2 AND [SOShipment].[Status] = 'N'
ORDER BY [SOShipLine].[ShipmentNbr], [SOShipLine].[ShipmentType], [SOShipLine].[LineNbr] OPTION(OPTIMIZE FOR UNKNOWN) /* PA.20.81.00 */"

Can you please add the logs from the Request Profiler? Go to request profiler check all the checkboxes on the left side, click start. Go to the page refresh the page and then click refresh on the request profiler page and click on SQL on the grid toolbar and add the logs to the question. — Samvel Petrosov
@Raj you have two PXParentAttributes which are setting SOShipLine as a parent, while you should have one PXParent setting SOShipLine and the second one setting SOShipment. Try changing [PXParent(typeof(Select<SOShipLine, Where<SOShipLine.shipmentNbr, Equal<Current<DeliverySchedule.shipmentNbr>>, And<SOShipLine.lineNbr, Equal<Current<DeliverySchedule.lineNbr>>>>>))] on the shipmentNbr with [PXParent(typeof(Select<SOShipment, Where<SOShipment.shipmentNbr, Equal<Current<DeliverySchedule.shipmentNbr>>>>))] — Samvel Petrosov

Brendan Brendan · Accepted Answer · 2019-07-11T19:44:08

It looks like your ShipmentNbr field should use a DB type such as PXDBString vs PXString. Change your attribute on your key field to see if this helps (to [PXDBString(15, IsKey = true)]). If you are trying to join it to other tables it is most likely failing in the join statements because its an unbound field as you have it posted in your question. Useage of an unbound field in a Join or Where clause might leave the other side of the = statement empty in the query generated for SQL.

Example:

#region ShipmentNbr
[PXDBString(15, IsKey = true)]
[PXDBDefault(typeof(SOShipLine.shipmentNbr))]
[PXParent(typeof(Select<SOShipLine, Where<SOShipLine.shipmentNbr, Equal<Current<DeliverySchedule.shipmentNbr>>, And<SOShipLine.lineNbr, Equal<Current<DeliverySchedule.lineNbr>>>>>))]
[PXUIField(DisplayName = "Shipment No.")]
public string ShipmentNbr { get; set; }
public class shipmentNbr : IBqlField { }
#endregion

Also you only need one PXParent to a table (you can have multiple PXParent - but different tables). You can use any field to place the PXParent attribute but should only need it once for a given table. (you have a PXParent to SOShipLine on ShipmentNbr and LineNbr)

BQL Join with a custom table giving Incorrect Syntax near '[keyword]' error at run time

1 Answers