Unanswered

How to update a sharepoint choice lookup field from a choices in a text field from a different sharepoint list

(0) Share

Report

Posted on by SpartaBI

Hi everyone,
I have this scenario (only putting the columns in play):
2 sharepoint lists:
1. Contracts

Title (Text)	Project Status (Choice)
301	Completed
302	Completed
303	On Going
304	On Going
305	On Going

2. Billable Hours:

Contract (Lookup from Contracts)

301

302

303

304

The app purpose is to view/add/edit records in the 'Billable Hours' list.
The form in play is connected to the 'Billable Hours' list.
Same form is used for edit and new scenarios. This is how it looks for the new mode:

For the contract field it has the auto generated combo box.
This is what I'm using now for the Items property:

If(
 EditForm1.Mode = FormMode.New,
 Filter(Choices([@'Billable Hours'].Contract), Value in Filter(Contracts, 'Project Status'.Value = "On Going").Title),
 Choices([@'Billable Hours'].Contract)
)

It's working as long as there was already a record of the contract in the list.
The problem is when I want to enter a record for a contract that is still doesn't have a record in the list, it won't be shown of course, so I want to pick it up from the contracts table title column.
I would like for the new mode that this dropdown would come from the title column in the contracts table that is of course filtered only to Project status = On Going (same logic as in the code I shared)

How to write the formula?

Thank you!

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

SpartaBI 48 on at

Like
a
(0)

Report
Copy link

Link copied!

Anyone? 😆
@WarrenBelz @Drrickryp @timl

I tried to give as much info as I could. Maybe it looks more scary then it is?
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@SpartaBI

In your SharePoint list, why not set the default property to On Going. There would be no need to change anything in the combobox inside the card. The DefaultSelectedItems property should be [Parent.Default]. Then when the form is in New mode, On Going would be in the combobox and when it is in edit mode, it will reflect any subsequent changes to that field in the record.
SpartaBI 48 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp thanks for you reply 🙂
I think maybe I didn't right that good.
The combo box is getting contract numbers (301, 302, ..)
The contract number is created in the contracts list as the title and then it is also a lookup field in the billable hours table.
In the card in Power Apps I don't need a default value for the new state, I need the available choices from the contract column. As of now the choices are coming from the billable hours list in the contract lookup column also as the input (and I use a filtering to only show contracts that are on going. All is working fine because I'm still using the same column type for the input and the destination:
If( EditForm1.Mode = FormMode.New, Filter(Choices([@'Billable Hours'].Contract), Value in Filter(Contracts, 'Project Status'.Value = "On Going").Title), Choices([@'Billable Hours'].Contract) )

But now, I want to change that list to come from the contracts table, where that column (contract column in contracts) is a text field, so I need to do something to the code that will accept a list of values from a text field and then be able to push it to a column of type lookup.
SpartaBI 48 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp ok, looks like I didn't need to do nothing. This is some crazy stuff :))
Can you please confirm this behaviour:
If( EditForm1.Mode = FormMode.New, Filter(Choices([@'Billable Hours'].Contract), Value in Filter(Contracts, 'Project Status'.Value = "On Going").Title), Choices([@'Billable Hours'].Contract) )

Although in the billable hours table there is still no record with 305 as a contract, the dropdown list will actually pick up all the possible values from the contracts column in the contracts table ! I'm coming from the Power BI and DAX world so I wouldn't expect it to have available values when the starting point of the function is:
Filter(Choices([@'Billable Hours'].Contract)
Cause there is still no 305 contract available in that list, but maybe because it's a lookup it still picks up all the available options, even if the record is still not there. (Reminder: this is a lookup column from the contracts table).