Initial value instead of valid if

[Ellie_F]

Hi have a validif statement that filters the drop down dependant on the previous selection,
Select(Address Book[Recordid],[Usermail]=USEREMAIL())
this leaves me only one name to select from a list and that name is the name of the user (to link my two records)
HOWEVER what I want to do instead is have that as the initial value with no need to change it.
Is this possible and if so please could someone explain how ?
any help greatly appreciated.

[LeventK]

Ellie_F:

Select(Address Book[Recordid],[Usermail]=USEREMAIL())

@Ellie_F
You should either enclose your expression with ANY(…) in the initial value or you can ref that table and use a de-ref expression

ANY(SELECT(Address Book[Recordid],[Usermail]=USEREMAIL()))

[Ellie_F]

@LeventK Thank you so so much. That is perfect .
I Truly appreciate your help

[LeventK]

Your welcome @Ellie_F, truly my pleasure to be helped of. Can you also mark-up my reply as solution if it did fit your needs & requirements? Thnx.

[Ellie_F]

I wonder if i could take this a step further as I am stumped . Please just let me know if i need to start a new thread instead.
I have three tables
Jobs
Sites (stores site addresses)
Address Book (stores my client names)

In Address Book , among other fields I have the following

Name - this is the label

Recordid – this is the key

In Sites, among other fields I have the following

Address – this is the label

Recordid – this is the key

ref – this is a ref field that references the Address Book

In Jobs I create a new job, I select Client Name (this is a ref field to Address Book).

I then click on a field titled site address, this shows me a filtered list of relevant sites using this SELECT(Sites[RecordID],[ref]=[_THISROW].[Client Name])

If I want to add a new site it takes me to a new screen to add the site details. In that new screen I need it to populate the ‘ref’ field with the previously selected client ‘Recordid’ from Address Book table.

In a different app I use this formula in the initial value based on the USEREMAIL of the person signed in - ANY(Select(Address Book[Recordid],[Usermail]=USEREMAIL()))
but in this app I need it to check against the selected client instead. I am stumped as to how to convert this formula for use here. Please please could you help me

[LeventK]

@Ellie_F
Could this be something that you are querying for? Check out the Ellie F menu view from my community sample app below. You are free to copy the app ( Record Changes ) from my portfolio as well from the below link:
https://www.appsheet.com/portfolio/245151

To see it in action: Just add a new Jobs record, select a client (ref) and click new from SITES (it’s a reverse ref). The dropdowns in the Sites form will bring and pre-fill the necessary information. After testing, provided the proposed solution fits your needs, please visit this post again and from this display, click the green checkbox next to your name in the main menu so that it will mark your proposal as SOLVED and your link will be filtered away from this sample app.

[Ellie_F]

many thanks for the example. In the example, it references the Job ID and each time an address is selected for us in the job it creates a new record in sites even if the address has already been used?. I want only a new sites record to be created if the site does not already exist. so need to store the client id with the site.
not sure i explain this very well.

[LeventK]

Hi @Ellie_F
I got your point very well. I will discuss this issue with my partner also and come back with a solution (hopefully)

[LeventK · Accepted solution]

Hello @Ellie_F,
I have updated my sample app as well. Provided you had taken a copy of the sample app, just delete it completely, and copy it again. You will notice the below expression in the Initial Value of [CLIENT_ID] column in Sites table:

=LOOKUP(INDEX(ORDERBY([Related Jobs],[_RowNumber],TRUE),1),"Jobs","JOB_ID","CLIENT")

This will solve the problem. As the iFramed app under this post is updated, you can test the new enhancement from there also.

[Ellie_F]

I really can’t thank you enough. I have been going around in circles for days trying to resolve this issue and you have provided the solution. Truly I am so very grateful for your time, your patience, and your very clear way of explaining what I needed to do. Thank you

[LeventK]

You’re welcome @Ellie_F, truly my pleasure to be helped of. Have a splendid day.