PowerApps Combine Tables for Search

[T_Findeisen]

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:

• SALESID (= SALESID IN SALESLINE)
• DELIVERYDATE
• SALESSTATUS
• DELIVERYPOSTALADDRESS (= RECID in LOGISTICSPOSTALADDRESS)

SALESLINE:

• SALESID (= SALESID IN SALESTABLE)
• NAME

LOGISTICSPOSTALADDRESS:

• RECID (= DELIVERYPOSTALADDRESS in SALESTABLE)

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

https://powerusers.microsoft.com/t5/General-Discussion/Using-ClearCollect-to-store-Table-column-valu...

Also: The Search implementation is not part of the problem (yet), it was just mentioned to make readers understand the situation.

[v-micsh-msft · Accepted 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