cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rprahalad
Helper V
Helper V

Many-Many Junction Table

I just want to confirm that I am thinking right and going the right way.

 

I have a States table and a Regions Table. I have a many-many 'RegionStates' junction  table that I custom created (and not the implicit one that DataVerse creates). RegionStates table has RegionID column (Lookup to Regions table) and a StateID column (Lookup to States table) and basically stores mapping data which is "One Region can have many States". 

 

Now, I have a Canvas app form where I have two drop downs, one for Regions and other for States. I also have other controls on the form and when the user clicks on Save on this form, I save this form to another Transactional table  that has a column named 'RegionStateId', which is a lookup reference to the above RegionState junction table. 

 

What is the best way for me to get the RegionState  row  on that form while saving it? Meaning If the user picks 'Region 1' from the regions Dropdown and a State of 'TX' from the States dropdown,  I need to get the 'RegionState' table row for that combo, so that I can save the form.

 

I am trying to do this in a split way:

 

Set(varSeletedState, LookUp(States, Description = ddlStates.Selected.Description);

Set(varSelectedRegion, LookUp(Regions, Name=ddlRegions.Selected.Name);

 

Now,

Set(varSelectedRegionState, LookUp(RegionStates, StateId = varSelectedState && RegionId = varSelectedregion);

is giving me an error.  Even though I know there is always just one row in RegionStates table for a combination of "a" region + "a" state, will PowerApps reject it and error out if I use LookUp?  Should I use Filter and First or how do I do a many-many query?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
rprahalad
Helper V
Helper V

I resolved it.  BTW, I forgot to mention that States is a cascading dropdown of regions. 

 

So,  this worked for me.

 

Set(varSelectedRegionState, LookUp(RegionStates, StateId.ID= varSelectedState.ID && RegionId.ID = varSelectedregion.ID); to get my regionState row. 

View solution in original post

2 REPLIES 2
rprahalad
Helper V
Helper V

I resolved it.  BTW, I forgot to mention that States is a cascading dropdown of regions. 

 

So,  this worked for me.

 

Set(varSelectedRegionState, LookUp(RegionStates, StateId.ID= varSelectedState.ID && RegionId.ID = varSelectedregion.ID); to get my regionState row. 

View solution in original post

Hi,

 

Thanks for your follow up post! I am glad that you were able to get this figured out. Hopefully this will prove useful for future communities users.

 

Regards,

 

Alex

 

-------

 

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

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

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

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (16,721)