Hello everybody,
I am pretty new to PowerApps and I am trying to implement a SearchFunction to List outstanding orders.
Requirements are to search for DeliveryAddress, ProductName, SalesId and DeliveryDate.
The necessary data is in 3 different tables for Dynamics AX.
SALESTABLE:
SALESLINE:
LOGISTICSPOSTALADDRESS:
After searching through the forums I found the AddColumn/LookUp commands and wrote the following Command into myGallery.Items:
AddColumns( Filter( '[dbo].[SALESTABLE]'; DATAAREAID = "USMF"; CUSTACCOUNT = CustAccountVar1; SALESSTATUS = 1 Or SALESSTATUS = 2 ); "DeliveryCity"; LookUp( '[dbo].[LOGISTICSPOSTALADDRESS]'; RECID = '[dbo].[SALESTABLE]'[@DELIVERYPOSTALADDRESS]; CITY ); "Product Name"; LookUp( '[dbo].[SALESLINE]'; SALESID = '[dbo].[SALESTABLE]'[@SALESID]; NAME ) )
I get the following errors in those lines:
RECID = '[dbo].[SALESTABLE]'[@DELIVERYPOSTALADDRESS]; SALESID = '[dbo].[SALESTABLE]'[@SALESID];
'=' gives invalid argument type
'[@...]' gives 'remotely not possible...make formula easier'
I am not quite sure what I did wrong because I think the structure is similar to those messages:
https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387
Also: The Search implementation is not part of the problem (yet), it was just mentioned to make readers understand the situation.
Solved! Go to Solution.
If the two tables contain the same field, then for better performance or misunderstanding, it is recommended to rename a field from one of the two tables.
Try the formula below:
ddColumns( Filter( '[dbo].[SALESTABLE]'; DATAAREAID = "USMF"; CUSTACCOUNT = CustAccountVar1; SALESSTATUS = 1 Or SALESSTATUS = 2 ); "DeliveryCity"; LookUp( '[dbo].[LOGISTICSPOSTALADDRESS]'; RECID = DELIVERYPOSTALADDRESS; CITY ); "Product Name"; LookUp(RenameColumns( '[dbo].[SALESLINE]';"SALESID";"SALESID1"); SALESID = SALESID1; NAME ) )
See if this works.
Regards,
Michael
If the two tables contain the same field, then for better performance or misunderstanding, it is recommended to rename a field from one of the two tables.
Try the formula below:
ddColumns( Filter( '[dbo].[SALESTABLE]'; DATAAREAID = "USMF"; CUSTACCOUNT = CustAccountVar1; SALESSTATUS = 1 Or SALESSTATUS = 2 ); "DeliveryCity"; LookUp( '[dbo].[LOGISTICSPOSTALADDRESS]'; RECID = DELIVERYPOSTALADDRESS; CITY ); "Product Name"; LookUp(RenameColumns( '[dbo].[SALESLINE]';"SALESID";"SALESID1"); SALESID = SALESID1; NAME ) )
See if this works.
Regards,
Michael
User | Count |
---|---|
174 | |
111 | |
86 | |
44 | |
42 |
User | Count |
---|---|
229 | |
117 | |
116 | |
74 | |
67 |