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(
CONSTRAINT PK_Head PRIMARY KEY (GUID)
create table ONCR.Position(
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.
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:
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:
View solution in original post
Hi @JonasH how are you progressing with this? Was the above reply helpful?
Hey @TopShelf-MSFT ,
The request was very helpful.
Have a nice day.
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
Learn how to build the business apps that you need.