cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonasH
Helper II
Helper II

Using SQL key

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 GUID2019-10-14 15_27_25-Unbenannt - Paint.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @JonasH how are you progressing with this? Was the above reply helpful?

 

@Anonymous 

Hey @Anonymous ,

The request was very helpful.

 

Have a nice day.

Jonas

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (5,191)