Checking if Record Exists

[Nunzie]

Hi all,

I will try to explain this as best as I can so it isn't too confusing. I'm trying to design an app that's using records/creating records in multiple sharepoint lists. I have looked up information on how to create relationships between my table but it seems I need to add the relationship entity, which I am blocked from doing by the Admin. Failing that I was going to try and use variables. Not as good I know but I am not sure what to do to work around it, and I am very new to PowerApps. I don't think I will be given the rights to add the relationship entity. Just a note the relationships would always be one to one (one project ID will only ever have one record on each table I mention below) related by the ProjectID.

I have the following sharepoint lists: Projects, SectionSelect, Section 1, Section 2.

On Screen1 I have a gallery of the Projects data source. I have a button that when selected it sets a variable of the ProjectID and Navigates to Screen2 which has a form with SectionSelect data source on it. This works fine, however, what I'm trying to do is if there is already a record in SectionSelect for this project ID, that it instead goes to Screen3, which will have multiple forms on it for Section 1 and Section 2 data sources.

If my button on Screen 1 is just setting the Variable and Navigating to Screen 2 it works fine, but when I try to add the IF statement into it, it all goes wrong, it's not recognizing the variable in the IF, presumable because it's all happening within the one statement? Maybe I'm just not putting things in the correct order or writing them properly.

Can anyone tell me if there is another way around relationships? If this will work and if so maybe how to write that code for that button?

Thank you in advance for your time and thoughts.

[Anonymous]

@Nunzie 

Posting your formulas would help, but you could try this on Screen 1 button

If(CountRows(Filter(SectionSelect, ProjectID = ThisItem.ProjectID))=0, Navigate(Screen2, ScreenTransition.Cover), Navigate(Screen3, ScreenTransition.Cover))

[Nunzie]

I had deleted my formula since it wasn't working, sorry. But the one you gave me works great, thank you. I was thinking it needed to include a variable. I suppose my only issue now is that when it goes to the new screen, getting it to pull up the records that are related, given I haven't stored a variable and there isn't actually a relationship built for them.

[Anonymous]

@Nunzie what relationship does Screen 3 data/forms have to either of the other 2 tables? Do these forms only need the Project ID or other data from Section Select as well?

Whatever variable code you had previously you could use again, just add it separately before my code, eg

[ your previous Var code ] ; [ my code ]

You could try this before my above code

Set(vProjectID, ThisItem.ProjectID)    // this sets a Project ID you can use throughout the app

// this sets a RECORD variable from SectionSelect that matches Project ID to use in the app 
Set(vSelectRecord, LookUp(SectionSelect, ProjectID = ThisItem.ID))

[Nunzie]

Hello,

So, each of the other screens will only share the Project ID, the other details on those lists are specific to those lists. This is why I wish I could build the relationship from the main list which is the gallery to the others. We are so restricted by the admins.

So I suppose the part I'm missing, is it actually showing the "related" record. It's going to the correct screen from your code, but it doesn't actually show a record. So the record that exists, based on the matching Project ID, I'd like to show those fields.

[Nunzie]

Lol. This is starting to drive me batty at this point. Now your code is not working for me, it's not navigating to the other page. I have the following:

If(CountRows(Filter('Test - SSD Sections', SSDProjID_FK = ThisItem.'Project ID'))=0, Navigate(SSDSectionPick.Form1.New,ScreenTransition.Fade), Navigate(SSD,ScreenTransition.Fade))

Just does nothing.

*Test-SSD Sections is the SectionSelect I mentioned above, I had just made the name simpler in my question.

[Anonymous]

@Nunzie  the Navigate() function takes a screen name as an input, you appear to be giving it a Form name?

Also, for the forms on Screen3, use something like this in the Items property of each form:

Lookup( section1List, RefProjectID = vProjectID) // use for Section 1 form

Lookup( section2List, RefProjectID = vProjectID) // use for Section 2 form

Make sure as well that the forms are in Edit mode if you are editing records, ie use this with your If():

EditForm(section1Form); EditForm(section2Form) ...

[Nunzie]

I REALLY appreciate your patience with me on this. Probably wasn't the easiest app to try as a first attempt.

I have amended the code, but it still isn't changing screens. I just want to get this working before trying to tackle your next recommendation re the forms. I've tested my variable works by having that code alone and using a text label to show the variable, it does. I've also tried the rest of the code starting from the If statement, it does not work. It is navigating to screens and not referencing the form so not sure were I am going wrong.

My code on my button is now:

Set(vProjectID, ThisItem.'Project ID'); If(CountRows(Filter('Test - SSD Sections', ParentProjID = ThisItem.'Project ID))=0, Navigate(SSDSectionPick, ScreenTransition.Fade), Navigate(SSD, ScreenTransition.Fade)

Also, forgive me, I renamed a couple of things to make them easier to differentiate for myself on the multiple lists.

I'm also getting the yellow triangle warning for this code for delegation. 

[Anonymous]

@Nunzie 

You've left a ')' off then end of your code to close off the If().

To get rid of the delegation we could use another variable 

[Nunzie]

AGH! It's so easy to miss those things.

But alas, I've added that and it still does not change screens, just does nothing. ☹️

Ready to throw in the towel at this point, something so simple and I can't get it to work. Doesn't give much hope for the more difficult tasks this will have to do.