cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMcAv
New Member

Setting foreign key (lookup field) programatically

Fairly new to PowerApps but loving the experience.

 

I am using CDS as database. I have two tables SurveyMaster and SurveyResponses. SurveyResponses contains a foreign key linking back to the SurveyMaster table. This is defined as a lookup in the SurveyResposnes table. I my thinking it should hold the PrimaryID of the related SurveyMaster record.

 

SurveyMaster

-----------------

PrimaryID                       Autonumber

Survey Name                 Text

Survey Status                 PickList

 

Survey Responses

-------------------------

PrimaryID                       Autonumber

User                               Text

SurveyID                        Lookup 

Response1                     Integer

Resposne2                     Integer

Response3                     Integer

 

When my app starts it finds the current survey (the first one with status = Open) from the SurveyMaster table. I then store the PrimaryID of the current survey in a collection.

 

ClearCollect(colSurveyID, First(Filter(colSurveys, (TeamID=1 && SurveyStatus="Open"))).PrimaryId);

 

When I look at the collection colSurveyID it reads as 000002

 

When I exit the app I want to write a record to the SurveyResponses table containing the responses the user has provided for the survey questions and I want the SurveyID field on the SurvyResponses record (that is the foreign key) to contain the PrimaryID of the current survey (obtained earlier and held in the colSurveyID collection).

  

Because I had problems with delegation to the CDS I read the SurveyResposnes table into a collection, check it for the existence of a record with this SurveyID and then if the record exists I update it or if the record does nor exist I write it.

 

So my code is (I've left later lines out to keep it simple) as this section demonstrates the problem

 

ClearCollect(ColResponsesTable,'SurveyResponses');

If(!IsEmpty(Filter(ColResponsesTable,SurveyID=colSurveyID)), ClearCollect(FoundIt,"Yes"), ClearCollect(FoundIt,"No"))

...

 

No matter how I try I am seeign an error triangle saying "Invalid Argument Type" and the equal sign highlighted in bold has a little red underline below it. 

 

Any advice on how to do this would be greaty appreciated.

 

Thanks

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JMcAv
New Member

OK so I've elarned quite a bit since I posted above.

 

The Foreign Key field on the child table (in my case Survey Responses) is not a single field but rather a record in its own right and the record has subfield. In my case it has 2 subfield

 

SurveyID.primaryID (this is the primary ID of the record in the related table)

SurveyID.SurveyName (this is here because I added SurveyName to the Default Lookup Group in the related table)

 

 

So the lookup fields are actually records!

 

Now when I create a new record in my Survey Responses table I need to populate the foreign key fields with the corresponding records from the related tables as follows (I'm using First to return a record here for simplicity but Filter would be more common)

 

SurveyID:First('Surveys')

 

Note - when accessing the foreign key field SurveyID on the Surevey Responses table the SurveyID.PrimaryID is populated but SurveyID.SurveyName is not. I don't know why. My workaround is then to do a lookup on the Survey table matching Primary ID to SurveyID.PrimaryID to get the SurveyName field.

 

I hope someone finds this useful.

 

 

View solution in original post

1 REPLY 1
JMcAv
New Member

OK so I've elarned quite a bit since I posted above.

 

The Foreign Key field on the child table (in my case Survey Responses) is not a single field but rather a record in its own right and the record has subfield. In my case it has 2 subfield

 

SurveyID.primaryID (this is the primary ID of the record in the related table)

SurveyID.SurveyName (this is here because I added SurveyName to the Default Lookup Group in the related table)

 

 

So the lookup fields are actually records!

 

Now when I create a new record in my Survey Responses table I need to populate the foreign key fields with the corresponding records from the related tables as follows (I'm using First to return a record here for simplicity but Filter would be more common)

 

SurveyID:First('Surveys')

 

Note - when accessing the foreign key field SurveyID on the Surevey Responses table the SurveyID.PrimaryID is populated but SurveyID.SurveyName is not. I don't know why. My workaround is then to do a lookup on the Survey table matching Primary ID to SurveyID.PrimaryID to get the SurveyName field.

 

I hope someone finds this useful.

 

 

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (54,046)