Hey,
I want to get data from a SQL-table using the foreign key.
The scope is to count the rows of the ONCR.Position with the HeadGUID linked with the CreatedBy column of ONCR.Head.
CountRows(Filter('[ONCR].[Position]', CheckedBy = User().FullName)) + CountRows(PosCache)
Here is a code snippet of the SQL statement to create both of the tables:
create table ONCR.Head( CheckedBy nvarchar(255), GUID nvarchar(255), CONSTRAINT PK_Head PRIMARY KEY (GUID) ); create table ONCR.Position( HeaderGUID nvarchar(64), GUID nvarchar(64) Constraint PK_Position PRIMARY KEY
CONSTRAINT FK_Head FOREIGN KEY (HeaderGUID) REFERENCES ONCR.Head (GUID) )
Example:
How to get the Name "Marco" by only having the GUID
To get the ammount of rows I tried stuff like:
CountRows(First(Filter('[ONCR].[Head]', GUID = '[ONCR].[Position]'.HeaderGUID)))
But it doesnt worked.
Is there a possibility to get the data? Or should I save the CreatedBy column in ONCR.Position too?
Thanks for your help.
Solved! Go to Solution.
Hi @JonasH ,
Do you want to get the Name "Marco" by only having the GUID?
Try this formula:
LookUp('[ONCR].[Head]', GUID = '[ONCR].[Position]'.HeaderGUID,CheckedBy)
The formula that you use "First(Filter(...))" is used to get the first record that meet the requirments.
So "CountRows(First(..))" will always get the result of "1".
I suggest you try the LookUp function.
The syntax is:
LookUp(tablename,condition,fieldname)
It means that get one field's value of the record that meet the condition(the first one record meet s the condition).
Here's a doc about this function for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup
Best regards,
Hi @JonasH ,
Do you want to get the Name "Marco" by only having the GUID?
Try this formula:
LookUp('[ONCR].[Head]', GUID = '[ONCR].[Position]'.HeaderGUID,CheckedBy)
The formula that you use "First(Filter(...))" is used to get the first record that meet the requirments.
So "CountRows(First(..))" will always get the result of "1".
I suggest you try the LookUp function.
The syntax is:
LookUp(tablename,condition,fieldname)
It means that get one field's value of the record that meet the condition(the first one record meet s the condition).
Here's a doc about this function for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup
Best regards,
User | Count |
---|---|
137 | |
136 | |
78 | |
73 | |
69 |
User | Count |
---|---|
222 | |
137 | |
78 | |
60 | |
56 |