By using this website, you agree to our Terms of Use (click here)
Hi,
I've been trying to build a GI to bring through component availability, which in turn lets them see what stock out they have. I've found that if a stock location hasn't had any stock, then the item won't show.
It seems simple as I'm joining the INKitSpecStkDet and INSiteStatus. The problem is when there isn't a line in the INSiteStatus for the Component Line. In the screenshot, you can see that only 6 lines appear for one location whereas there are 7 lines for the Kit Spec. I even tried to add a Warehouse Detail for the missing location, but that doesn't work for me. Lastly, I added the INSiteStatusSummary to see if that helped, as it is the main table for the Warehouse Details tab. This didn't help either.
Any suggestions? I can provide the code and xml file if needed.
Did you ever get an answer to this? I needing the same thing.
This has to do with how databases join tables for queries. If you use an inner join, it will only return results if they exist in both tables. If you use a left or right join, it will return all records from the left or right table and only those from the other table where there is a match. Where there is no match, it would return a null.
It sounds like you want the database to return the complete list of warehouses, the complete list of kits and the count of where those two intersect showing a 0 if there are none.
In traditional relational databases, I would do this with more than one query where the first query is an input in the second. The first would be a crossjoin query across warehouses & kits to get the complete cross product of both (call this output1). I would then create a query that totals kits per warehouse (call this output2). Lastly, I would create a query of output1 & output2 where I join them on the warehouse ID and kit ID. I would then have a function on the total iif(isnull(total),0,total)
I don't think you can do this with a generic inquiry. But I looks like you might be able to do this with BQL - https://ggc.acumatica.com/(W(1))/Help?CompanyID=GGC&ScreenId=ShowWiki&pageid=13001ef0-91f0-4094-b924-d1114c3e8e41
Hi @brandi-buff,
Thanks for your reply. I had managed to replicate this query in MySQL using the left (or right) joins based on the Tables. I think GIs should be able to do this, so I will take a look at @busyfritz answer and loop back around to the BQL if not.
I think also that you may have added a link to a Customer site or were you trying to show the wiki article?
Regards
Nick SM
Here's a GI that will do this, but they don't include SO booked. That is a feature that would need to be added:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<data-set>
<relations format-version="3" relations-version="20210225" main-table="GIDesign" stable-sharing="True" file-name="(Name)">
<link from="GIFilter (DesignID)" to="GIDesign (DesignID)" />
<link from="GIGroupBy (DesignID)" to="GIDesign (DesignID)" />
<link from="GIMassAction (DesignID)" to="GIDesign (DesignID)" />
<link from="GIMassUpdateField (DesignID)" to="GIDesign (DesignID)" />
<link from="GINavigationScreen (DesignID)" to="GIDesign (DesignID)" />
<link from="GINavigationParameter (DesignID, NavigationScreenLineNbr)" to="GINavigationScreen (DesignID, LineNbr)" />
<link from="GINavigationCondition (DesignID, NavigationScreenLineNbr)" to="GINavigationScreen (DesignID, LineNbr)" />
<link from="GIOn (DesignID, RelationNbr)" to="GIRelation (DesignID, LineNbr)" />
<link from="GIRecordDefault (DesignID)" to="GIDesign (DesignID)" />
<link from="GIRelation (DesignID, ParentTable)" to="GITable (DesignID, Alias)" />
<link from="GIRelation (DesignID, ChildTable)" to="GITable (DesignID, Alias)" />
<link from="GIResult (DesignID)" to="GIDesign (DesignID)" />
<link from="GIResult (ObjectName, DesignID)" to="GITable (Alias, DesignID)" />
<link from="GISort (DesignID)" to="GIDesign (DesignID)" />
<link from="GITable (DesignID)" to="GIDesign (DesignID)" />
<link from="GIWhere (DesignID)" to="GIDesign (DesignID)" />
<link from="SiteMap (Url)" to="GIDesign (DesignID)" type="WeakByUrl" linkname="toDesignById" baseurl="~/GenericInquiry/GenericInquiry.aspx" paramnames="id" />
<link from="SiteMap (Url)" to="GIDesign (Name)" type="WeakByUrl" linkname="toDesignByName" baseurl="~/GenericInquiry/GenericInquiry.aspx" />
<link from="ListEntryPoint (ListScreenID)" to="SiteMap (ScreenID)" />
<link from="SiteMap (ScreenID)" to="GIDesign (PrimaryScreenIDNew)" linkname="to1Screen" />
<link from="FilterHeader (ScreenID)" to="SiteMap (ScreenID)" />
<link from="FilterRow (FilterID)" to="FilterHeader (FilterID)" />
<link from="PivotTable (NoteID)" to="FilterHeader (RefNoteID)" />
<link from="PivotField (ScreenID, PivotTableID)" to="PivotTable (ScreenID, PivotTableID)" />
<link from="MUIScreen (NodeID)" to="SiteMap (NodeID)" />
<link from="MUIWorkspace (WorkspaceID)" to="MUIScreen (WorkspaceID)" type="FromMaster" linkname="workspaceToScreen" split-location="yes" updateable="True" />
<link from="MUISubcategory (SubcategoryID)" to="MUIScreen (SubcategoryID)" type="FromMaster" updateable="True" />
<link from="MUITile (ScreenID)" to="SiteMap (ScreenID)" />
<link from="MUIWorkspace (WorkspaceID)" to="MUITile (WorkspaceID)" type="FromMaster" linkname="workspaceToTile" split-location="yes" updateable="True" />
<link from="MUIArea (AreaID)" to="MUIWorkspace (AreaID)" type="FromMaster" updateable="True" />
<link from="MUIPinnedScreen (NodeID, WorkspaceID)" to="MUIScreen (NodeID, WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
<link from="MUIFavoriteWorkspace (WorkspaceID)" to="MUIWorkspace (WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
<link from="GIDesign (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIFilter (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIFilter (NoteID)" to="GIFilterKvExt (RecordID)" type="RowKvExt" />
<link from="GIGroupBy (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIOn (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIRelation (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIResult (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIResult (NoteID)" to="GIResultKvExt (RecordID)" type="RowKvExt" />
<link from="GISort (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GITable (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIWhere (NoteID)" to="Note (NoteID)" type="Note" />
<link from="FilterHeader (NoteID)" to="Note (NoteID)" type="Note" />
<link from="FilterHeader (NoteID)" to="FilterHeaderKvExt (RecordID)" type="RowKvExt" />
</relations>
<layout>
<table name="GIDesign">
<table name="GIFilter" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="GIFilterKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="GIGroupBy" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIMassAction" uplink="(DesignID) = (DesignID)" />
<table name="GIMassUpdateField" uplink="(DesignID) = (DesignID)" />
<table name="GINavigationScreen" uplink="(DesignID) = (DesignID)">
<table name="GINavigationParameter" uplink="(DesignID, LineNbr) = (DesignID, NavigationScreenLineNbr)" />
<table name="GINavigationCondition" uplink="(DesignID, LineNbr) = (DesignID, NavigationScreenLineNbr)" />
</table>
<table name="GIRecordDefault" uplink="(DesignID) = (DesignID)" />
<table name="GISort" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GITable" uplink="(DesignID) = (DesignID)">
<table name="GIRelation" uplink="(DesignID, Alias) = (DesignID, ParentTable)">
<table name="GIOn" uplink="(DesignID, LineNbr) = (DesignID, RelationNbr)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIResult" uplink="(Alias, DesignID) = (ObjectName, DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="GIResultKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIWhere" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="SiteMap" uplink="(DesignID) = (Url)" linkname="toDesignById">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="SiteMap" uplink="(Name) = (Url)" linkname="toDesignByName">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="SiteMap" uplink="(PrimaryScreenIDNew) = (ScreenID)" linkname="to1Screen">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="MUIWorkspace">
<table name="MUIFavoriteWorkspace" uplink="(WorkspaceID) = (WorkspaceID)" />
</table>
<table name="MUISubcategory" />
<table name="MUIArea" />
</layout>
<data>
<gidesign>
<row DesignID="7d8698d4-c90e-4d48-8f81-da745be8f7fe" Name="KitAssyAvail" FilterColCount="3" PageSize="0" ExportTop="0" NewRecordCreationEnabled="0" MassDeleteEnabled="0" AutoConfirmDelete="0" MassRecordsUpdateEnabled="0" MassActionsOnRecordsEnabled="0" ExposeViaOData="1" ExposeViaMobile="1" ShowDeletedRecords="1" NotesAndFilesTable="$<None>">
<gifilter LineNbr="1" IsActive="1" Name="IncStockKits" FieldName="InventoryItem.stkItem" DataType="string" DisplayName="Show Stock Kits" IsExpression="1" ColSpan="1" Required="1" />
<gigroupby LineNbr="4" IsActive="1" DataFieldName="INSiteStatus.siteID" />
<gigroupby LineNbr="3" IsActive="1" DataFieldName="INKitSpecHdr.revisionID" />
<gigroupby LineNbr="2" IsActive="1" DataFieldName="INKitSpecHdr.descr" />
<gigroupby LineNbr="1" IsActive="1" DataFieldName="INKitSpecHdr.kitInventoryID" />
<ginavigationscreen Link="GI000101" LineNbr="2" SortOrder="2" WindowMode="W" IsActive="1">
<ginavigationparameter LineNbr="1" FieldName="KitID" ParameterName="INKitSpecHdr.kitInventoryID" IsExpression="0" />
</ginavigationscreen>
<ginavigationscreen Link="IN209500" LineNbr="1" SortOrder="1" WindowMode="W" IsActive="1">
<ginavigationparameter LineNbr="2" FieldName="RevisionID" ParameterName="INKitSpecHdr.revisionID" IsExpression="0" />
<ginavigationparameter LineNbr="1" FieldName="KitInventoryID" ParameterName="INKitSpecHdr.kitInventoryID" IsExpression="0" />
</ginavigationscreen>
<gisort LineNbr="1" IsActive="1" DataFieldName="InventoryItem.inventoryID" SortOrder="A" />
<gitable Alias="InventoryItem" Name="PX.Objects.IN.InventoryItem">
<girelation LineNbr="1" ChildTable="INKitSpecHdr" IsActive="1" JoinType="L">
<gion LineNbr="1" ParentField="inventoryID" Condition="E" ChildField="kitInventoryID" Operation="A" />
</girelation>
</gitable>
<gitable Alias="INSiteStatus" Name="PX.Objects.IN.INSiteStatus">
<giresult LineNbr="10" SortOrder="5" IsActive="1" Field="=IIf(IIf( [INSiteStatus.QtyAvail]=0, 0, Floor([INSiteStatus.QtyAvail] / [INKitSpecStkDet.DfltCompQty]))<=0,0,Floor([INSiteStatus.QtyAvail] / [INKitSpecStkDet.DfltCompQty]))" Caption="Kit Quantity Available" IsVisible="1" DefaultNav="0" AggregateFunction="MIN" TotalAggregateFunction="SUM" NavigationNbr="2" QuickFilter="0" FastFilter="0" RowID="e3d93a2d-0953-43a1-b494-8d11e8b38963" />
<giresult LineNbr="8" SortOrder="4" IsActive="1" Field="siteID" Caption="Warehouse" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="88daea62-b9e2-4cb4-87e8-a94b71b54021" />
</gitable>
<gitable Alias="INKitSpecStkDet" Name="PX.Objects.IN.INKitSpecStkDet">
<girelation LineNbr="3" ChildTable="INSiteStatus" IsActive="1" JoinType="L">
<gion LineNbr="4" ParentField="compInventoryID" Condition="E" ChildField="inventoryID" Operation="A" />
</girelation>
</gitable>
<gitable Alias="INKitSpecHdr" Name="PX.Objects.IN.INKitSpecHdr">
<girelation LineNbr="2" ChildTable="INKitSpecStkDet" IsActive="1" JoinType="L">
<gion LineNbr="3" ParentField="revisionID" Condition="E" ChildField="revisionID" Operation="A" />
<gion LineNbr="2" ParentField="kitInventoryID" Condition="E" ChildField="kitInventoryID" Operation="A" />
</girelation>
<giresult LineNbr="3" SortOrder="3" IsActive="1" Field="revisionID" Caption="Kit Revision" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="db7d5687-8234-45b6-8443-ddbca8627d18" />
<giresult LineNbr="2" SortOrder="2" IsActive="1" Field="descr" Caption="Description" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="8b7a0a3f-8754-41fc-80f2-91a312f08e1e" />
<giresult LineNbr="1" SortOrder="1" IsActive="1" Field="kitInventoryID" Caption="Kit Name" Width="200" IsVisible="1" DefaultNav="0" NavigationNbr="1" QuickFilter="0" FastFilter="0" RowID="f6d225d3-2eab-488f-b739-0d6b9bccb44b" />
</gitable>
<giwhere LineNbr="4" IsActive="1" DataFieldName="InventoryItem.stkItem" Condition="E" IsExpression="1" Value1="False" Value2="False" CloseBrackets=")" Operation="A" />
<giwhere LineNbr="3" IsActive="1" OpenBrackets="(" DataFieldName="InventoryItem.stkItem" Condition="E" IsExpression="0" Value1="[IncStockKits]" Operation="O" />
<giwhere LineNbr="2" IsActive="1" DataFieldName="INKitSpecHdr.isActive" Condition="E" IsExpression="1" Value1="True" Value2="False" CloseBrackets=")" Operation="A" />
<giwhere LineNbr="1" IsActive="1" OpenBrackets="(" DataFieldName="InventoryItem.kitItem" Condition="E" IsExpression="1" Value1="True" Value2="False" Operation="A" />
<sitemap linkname="toDesignById">
<row Title="Kit Assembly Availability" Url="~/GenericInquiry/GenericInquiry.aspx?id=7d8698d4-c90e-4d48-8f81-da745be8f7fe" ScreenID="GI000100" NodeID="1b190862-671b-4f0c-90b7-3c5854bffd72" ParentID="00000000-0000-0000-0000-000000000000">
<muiscreen IsPortal="0" WorkspaceID="6557c1c6-747e-45bb-9072-54f096598d61" Order="140" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4">
<muipinnedscreen IsPortal="0" Username="" IsPinned="1" />
</muiscreen>
</row>
</sitemap>
<note NoteText="For each
Qty of kit component on hand 157
Divided by qty required for kit 8
19.625
rounded down to the nearest integer 19
Next
MIN value of all loops above
" GraphType="PX.Data.Maintenance.GI.GenericInquiryDesigner" EntityType="PX.Data.Maintenance.GI.GIDesign" />
</row>
</gidesign>
<muiworkspace>
<row IsPortal="0" WorkspaceID="6557c1c6-747e-45bb-9072-54f096598d61" Order="284" Title="Inventory" Icon="local_shipping" AreaID="62cfd5dc-8eb9-4e92-bbcd-e0a99eb5e5df" ScreenID="WSIN0000" IsSystem="0">
<muifavoriteworkspace IsPortal="0" Username="" IsFavorite="1" />
</row>
</muiworkspace>
<muisubcategory>
<row IsPortal="1" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
<row IsPortal="0" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
</muisubcategory>
<muiarea>
<row IsPortal="0" AreaID="62cfd5dc-8eb9-4e92-bbcd-e0a99eb5e5df" Order="20" Name="Operations" />
</muiarea>
</data>
</data-set>
Hi @busyfritz,
I'll check this out in the morning and I can see the joins in the xml, so that might help. I can also see the IIF field for Availability which I added in mine, but not as complex. Thanks for the answer though as it is always nice to find out.
Regards
Nick SM
@busyfritz This was perfect. I added the component table and then added it as a group by and this was exactly what the client wanted to see. I appreciate your work on this.
Hello. I have gotten something like this working using a Cross Join. Usually you want to avoid Cross joins because they can be pretty destructive, but they do have their uses. In this case, you can start with INSite CROSS APPLY InventoryItem. The results here being a list of every inventory item and every warehouse. LEFT JOIN in INSiteStatus ON INSiteStatus.SiteID = INSIte.SiteID AND INSiteStatus.InventoryID = InventoryItem.InventoryID and use ISNULL([INSiteStatus.Qty_____],0) to get the Qtys you need.
Your conditions are super important here, because you want to target as few InventoryItem/Site records as possible. If you can add in parameters to only look at one Site or one Kit at a time, you should do that.
I'd love to see replenishment Qtys worked into here!
Online Members
