Dropdown is not responding as expected

(0) Share

Report

Posted on by DA-28080959-0

Hi Experts,

1. using excel as a data source.

2. one column (Invoice timelines) has a comma separated values (BD1, BD2, Last Day)
3. i have created a gallery with customer names and forms with other fields - in the form i have used the above column to split it and used as a dropdown. item formula below:

ForAll(
Sort (split (
    Text (
        DataCardValue7.Value),","),Value,SortOrder.Ascending), Value)

 
4.based on this dropdown, another text box (invoice SLA date) should reflect.

5.but the split is not working properly, only one value is displaying even multiple values are there for the customer in the first image. Always showing first row value (BD1) - BD7 is not listed in dropdown.



Invoice SLA Date text box value:
First(
        Filter(
            Table1 
            ,Table1[@'InvoiceTimeLines'] = DropdownCanvas4.Selected.Value && Month = DropdownCanvas3.Selected.Value   // filtering each month dates, so that Month dropdown added here.
        )
    ).'InvoiceSLADate'
 
please help to solve this like why all the customer related timelines are not listing?
 
Thanks
DK

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

Sort by

WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @DA-28080959-0,

A couple of things here - firstly what you have in your drop-down Items should work (and does so here when tested), however you can shorten it to this
Sort( Split ( TextInputCanvas4.Value, "," ), Value, SortOrder.Ascending )

so that may be worth trying.

The next question is how many records do you have in your Excel table (or more importantly, how many is it likely to have in the future) and what type of field is Month (I cannot see it in your posted structure) and what is the Items of the drop-down filtering this.

Was this reply helpful? Yes No
DA-28080959-0 56 on at

Like (0)

Report
Copy link

Link copied!

hi @WarrenBelz ,

Altered my split fx:

Sort(

Split(

DataCardValue7.Value,

","

),

Value,

SortOrder.Ascending

)

currently i have 3446 rows in excel file with duplicate customers, hence i have used distinct to take unique customers in gallery (228) , in future we will have to connect snowflake database and use. mostly there will be no increase of rows in future, so we can take 4000 rows approx without distinct.

Month name also coming from excel column (General type)
Month dropdown Items are:

["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

Defaultselecteditems : // to display current month when we open app

LookUp(["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"],Value=Text(Today(),"[$-en-US]mmm"))

Was this reply helpful? Yes No
WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @DA-28080959-0,

OK - that explains a lot. Firstly Excel is a terrible data source for Power Apps, particularly as the record numbers grow, but in this case, that is not your total issue if you are wanting to use Distinct() (which you did not mention before and have not provided any code). This function is not supported by Delegation - it is actually a "local" process, meaning the largest number of rows you can address with the function is 2,000 (the maximum available for your Data Row Limit).

Moving on to the rest of the issue, you can firstly get the month list for your drop-down with this in the Items
ForAll( Sequence(12), Text( Date(2025,Value,1), "[$-en-US]mmm" ) )

and you really do not need to look all of that up again if you simply want the current month in the DefaultSelectedItems
{Value: Text(Today(),"[$-en-US]mmm")}

Now your main issue - as I mentioned above, Excel is not a very query-friendly data source, particularly as the queries get complex and/or the record number grow. So we can break the query down in stages - as I assume your records will never have more than 500 per month (increase your Data Row Limit if so), try this
With( { _Data: Filter( Table1, Month = DropdownCanvas3.Selected.Value ) }, LookUp( _Data, 'InvoiceTimeLines' = DropdownCanvas4.Selected.Value ).'InvoiceSLADate' )

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like♥.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
DA-28080959-0 56 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz thanks a lot for your detailed solution.
the last fx you have given for InvoiceSLADate text box, but my real issue here is about the InvoiceTimelines dropdown listing.

below you can see that only BD7 is listed, "Last Day" is not listing. but as per above data source table, 2 rows for customer with different timelines.
InvoiceTimelines is the one split from comma separated text and converted to dropdown.

Was this reply helpful? Yes No
WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @DA-28080959-0,

OK - I thought that was solved - I see that the drop-down is contained on a Form - where is DataCardValue7 and what is in it ? Thse values seem to be coming from the 'Invoice Time Lines' field in the Table. If the Form is based on the same record, then
Sort( Split ( ThisItem.'Invoice Time Lines', "," ), Value, SortOrder.Ascending )

EDIT: I will be offline now due to timezone (UTC + 10) - I will pick up your response in the morning.

Was this reply helpful? Yes No
DA-28080959-0 56 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz Actually, invoice time lines is also a part of form, but when it converts to dropdown, i have created a custom card for dropdown and applied the same datacardvalue7 (for invoice time lines) in the dropdown. I have disabled the visibility of the data card, so that it is not showing in the form.

Thisitem.InvoiceTimelines function is not available inside the split function.
Tried Text () also, no luck.

Was this reply helpful? Yes No
WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @DA-28080959-0,

So I am absolutely clear on what you have posted, you have a Form with the drop-down in question in a Data Card with the ‘Invoice Time Lines’ field as the DataSource. This Data Card also contains a Text Input DataCardValue7 you mentioned and (unhide it for the moment) you can see the comma-delimited values you expect in the drop-down (I do not see any comma-seperated values in that field in the table you initially posted).

What is not making sense to me if my assumption is correct is both why ThisItem does not work and also why the split code does not work if you can see the content I mentioned. Can you also confirm the Item and DataSource of the Form and the accuracy of my assumptions.

Was this reply helpful? Yes No
DA-28080959-0 56 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz

Form datasource also a excel table : Table1
Form Items :

LookUp(Table1,Customer = Gallery2.Selected.Value && Month = DropdownCanvas3.Selected.Value)

yes sorry, the invoice time lines are not comma separated but multiple , we need to use this as a drop down (BD7,Last Day) based on the customer selection from the gallery.

so based on the time lines selecttion , SLA date should reflect .
split done for created date already.

ForAll(

Split(

DataCardValue1.Value,","),If(

IsBlank(

Trim(Value)) || Trim(Value)="",

"No Date Available",Trim(Value)

)

)

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @DA-28080959-0,

I think we finally got there - if you are simply after a Distinct list of values in the field 'Invoice Time Lines' from the records filterted by the user

Sort( Distinct( Gallery2.AllItems, 'Invoice Time Lines' ), Value )

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like♥.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
DA-28080959-0 56 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz thanks warren, i have tried this and the column invoicetimelines is not recognized, hence i have used collection in App Onstart and used the below fx for the same:

SortByColumns(Distinct(Filter(colDatasource,Customer=DataCardValue5.Value),InvoiceTimeLines

),"Value"

) // based on the customer, the InvoiceTimeLines will display , it works well.

I want to clarify one thing with you, We are having an issue with snowflake connectors for us inside the organization.Do we have any alternate ways to fetch the data from snowflake database?

like collections, power automate ..

if you have any solution please share with me to achieve that.

thanks.

Was this reply helpful? Yes No