Navigate user to particular screen depending on lookup via SharePoint list

[EitanKGCS]

Hi,

I am trying to create a switch function that will navigate to different screens depending on which user clicks it. 

I have a sharepoint list with all our staff in the person column, which team they are in and a third column with which screen it should navigate to. 

Is it possible to create a lookup and switch function that when a user logs in to the app it looks up on the sharepoint list the user, then when the click the button to go to the next screen it looks up in the navigation column of the sharepoint list which screen to navigate to? 

So far I have something like this:

List name - Team list

Name column - Full name

Navigation column - Navigate1

Team column - Team

If(
Lookup('Teams list'.'Full name' = User().FullName)
),
Switch(
LookUp('Teams list', Navigate1.Value = "Screen 2", Navigate(Screen2,ScreenTransition.Fade),
LookUp('Teams list', Navigate1.Value = "Screen 3", Navigate(Screen3,ScreenTransition.Fade),
)

Can someone please help me see what I am missing here?

Thanks!

[RandyHayes]

@EitanKGCS 

Your Switch statement is incorrect.  It takes the first parameter as what it is "switching" on, and then the conditions to match and the action to happen in pairs after that.

Your formula should be:

With(Lookup('Teams list'.'Full name' = User().FullName),
    Switch(Navigate1.Value,
        "Screen 2", Navigate(Screen2,ScreenTransition.Fade),
        "Screen 3", Navigate(Screen3,ScreenTransition.Fade)
    )
)

I hope this is helpful for you.

[EitanKGCS]

Thank you for the formula amendment but it still doesn't seem to work. It says the function With has some invalid arguments. I think everything within the with formula works but the with statement looks incorrect. Do you have any suggestions as to why this might be?

Thanks!

[AJ_Z]

With({Navigate1:Lookup('Teams list'.'Full name' = User().FullName)},
    Switch(Navigate1.Value,
        "Screen 2", Navigate(Screen2,ScreenTransition.Fade),
        "Screen 3", Navigate(Screen3,ScreenTransition.Fade)
    )
)

try this its still Randys solution but i added the name to the with

[EitanKGCS]

I have tried your formula which makes sense with what I know with formula's should look like. 

The error showing up is 'Invocation of unknown or unsupported function.'

The .Value error is invalid use of '' but I haven't even used those at that point so confused as to why that is showing up.

Do you have any idea why this might be? 

Really appreciate the help!

[RandyHayes]

@EitanKGCS 

That formula has an error.

Please change to the following:

With(LookUp('Teams list'.'Full name' = User().FullName),
    Switch(Navigate1.Value,
        "Screen 2", Navigate(Screen2,ScreenTransition.Fade),
        "Screen 3", Navigate(Screen3,ScreenTransition.Fade)
    )
)

EDIT: corrected a wrong field reference above.

[EitanKGCS]

This is the same formula you gave originally which has the errors:

'The function 'With' has some invalid arguments.' and

'Invalid number of arguments: received 1, expected 2-3.'

Wouldn't it need the reference suggested by @AJ_Z?

[RandyHayes · Accepted solution]

@EitanKGCS 

Actually, it's not the same formula, there is a change in it, but I see I also copied a different earlier mistake in it.

Should be:

With(LookUp('Teams list', 'Full name' = User().FullName),
    Switch(Navigate1.Value,
        "Screen 2", Navigate(Screen2,ScreenTransition.Fade),
        "Screen 3", Navigate(Screen3,ScreenTransition.Fade)
    )
)

And, no, With does not need a reference aka variable name.  When you use a record as a With source, it is then available by direct reference in the entire With scope.

So, in this case, the LookUp returns the record and it is directly accessible (i.e. the Navigate1.Value) in the entire formula.

Sorry for the past typos!! 

[RandyHayes · Accepted solution]

@EitanKGCS 

Just to wrap up so you're not totally confused.  The original formula I provided would have worked fine with two corrections.  1) LookUp was spelled wrong and 2) There was a period instead of a comma in the LookUp parameters.

The suggestion by @AJ_Z would not have worked because the Navigate1 variable would have referenced the entire record (which in my last post...this is totally optional when working with a record) and the Switch statement was referencing the Navigate1.Value - which does not exist because Navigate1 is the entire record.  If it had been Navigate1.Navigate1.Value it would have worked as well...just more things to type that aren't needed.

However, it would have also failed for the same two reasons that my original did!!

So again, it looks like my first formula introduced the chain of copy/pastes that just perpetuated the original errors.  Sorry on that. 

You should be good now though!

[EitanKGCS]

Thank you so much for the help!  There was one error in the formula which I amended and now works perfectly

With(LookUp('Teams list', 'Full name'.Email = User().Email),

As it is a record it required a .'X'. Once that matched what it was looking for it worked exactly how I wanted it to.

Thanks everyone!