Reference Table Field with DropDown

[iwonder]

Thank you for taking the time to read my question.

I have this formula for the Items of a Gallery.

DelType = DropDown

PriceFilter = Text Input

Filter(ProspectFreightRates,Substitute(Substitute(DelType.SelectedText.Value," ",""),"Bulk","") = PriceFilter.Text)

It does not work.

If I replace Substitute(Substitute(DelType.SelectedText.Value," ",""),"Bulk","")  with the actual field name then it works (selectable as I type in the field name). 

I was thinking I could do this instead of having a bunch of If() statements for each of the values in DelType with each condition having the typed in field name.

Thanks

[RandyHayes]

@iwonder 

Unfortunately, you can not reference a control or field/column by reference like that in a Filter statement.

Can you explain what you are trying to get to.  In many cases you will not need a bunch of If statements.

I hope this is helpful for you.

[iwonder]

Hi Randy, thank you for your reply.

I'm trying to figure out a nice way to manage a SharePoint List of prices per delivery size. My drop down is the different delivery sizes, then I have an Input Box where the user can type the dollar amount. I want to filter my Gallery for the different delivery sizes and prices. Then they can select which ones they want to update. This way they don't have to edit each price separately, but rather they can type in one new value and I can apply it to all the selected rows in the Gallery.

Here is what is working so far: 

If(DelType.SelectedText.Value = "Small Bulk Load",Filter(ProspectFreightRates,SmallLoad = PriceFilter.Text),DelType.SelectedText.Value = "Belly Dump",Filter(ProspectFreightRates,BellyDump = PriceFilter.Text),DelType.SelectedText.Value = "Large Bulk Load",Filter(ProspectFreightRates,FullHalfLoads = PriceFilter.Text),DelType.SelectedText.Value = "Bag Truck",Filter(ProspectFreightRates,BagTruck = PriceFilter.Text))

Then on the label in my gallery I do something similar to change the Text value source

If(DelType.SelectedText.Value = "Small Bulk Load",ThisItem.SmallLoad,DelType.SelectedText.Value = "Belly Dump",ThisItem.BellyDump,DelType.SelectedText.Value = "Large Bulk Load",ThisItem.FullHalfLoads,DelType.SelectedText.Value = "Bag Truck",ThisItem.BagTruck)

This way I don't have to have a bunch of extra data in my Gallery rows making it messy.

Thanks

[RandyHayes]

@iwonder 

Yep, I understand.  

So a couple of things.  One avoid referencing SelectedText on dropdown/combobox controls as this is a Deprecated property (one less thing you will have to come back and fix once they turn it off).

Second, consider the following for your Filter:

Filter(ProspectFreightRates, 
   Switch(DelType.Selected.Value,
       "Bag Truck", BagTruck = PriceFilter.Text
       "Small Bulk Load", SmallLoad = PriceFilter.Text,
       "Belly Dump", BellyDump = PriceFilter.Text,
       "Large Bulk Load", FullHalfLoads = PriceFilter.Text
   )
) 

It is best to avoid using multiple Filter statements in a formula when you can as you create more redundant formula than you need and you risk having an issue with mismatched schemas (in certain situations).

For your Label, the following is a little easier to work with:

Switch(DelType.Selected.Value,
    "Small Bulk Load", ThisItem.SmallLoad,
    "Belly Dump", ThisItem.BellyDump,
    "Large Bulk Load", ThisItem.FullHalfLoads,
    "Bag Truck",ThisItem.BagTruck
)

All of these should give you what you want.  Ideally in many cases, I try to get all of this into the actual dropdown and then just reference that, but that gets a little more involved. The above should be fairly straighforward.

[iwonder]

Excellent! Thank you so much for your tip and for the formulas. 

Note: there is a comma missing in the first Switch() statement, I've fixed that.

Filter(ProspectFreightRates, Switch(DelType.Selected.Value, "Bag Truck", BagTruck = PriceFilter.Text, "Small Bulk Load", SmallLoad = PriceFilter.Text, "Belly Dump", BellyDump = PriceFilter.Text, "Large Bulk Load", FullHalfLoads = PriceFilter.Text )) 

I've implemented your formulas, but no data is returned. On the Gallery Items filter I get a delegation warning... May not work on large data sets. I just realized I failed to mention that my table is a SharePoint list with over 2000 records.

As a test I entered a filter that would work for the very first row in the SP list but still nothing is returned.

Thoughts?

Question: What do you use when you insert code examples into the forum? 

[RandyHayes]

@iwonder 

Yep, that certainly would not be delegable.  This is a challenge with switching columns like that.

By the way, yes, very good chance that I would miss a comma or something.  I type these all in by hand when replying, so I don't have the luxury of the formula editor to show mistakes.  Other than that, I just insert a formula in a codeblock in the forum.

So, let's get rid of the delegation issue.

First question - do you have a narrowing filter that can be applied?  A narrowing filter is a delegable pre-filter that will return less than 2000 records.  For example, a record might have an "Open" yes/no column in it to designate that the item is open and active.  There may be never more than 2000 open items at a time.  IF this was a condition that applied to you, then you can perform a pre-filter on that : Filter(ProspectFreightRates, Open) and then use the results of that for the rest of the filter.  This works perfectly.

So taking that concept:

With({preFilter: Filter(ProspectFreightRates, Open)},

    Filter(preFilter, 
        Switch(DelType.Selected.Value,
           "Bag Truck", BagTruck = PriceFilter.Text,
           "Small Bulk Load", SmallLoad = PriceFilter.Text,
           "Belly Dump", BellyDump = PriceFilter.Text,
           "Large Bulk Load", FullHalfLoads = PriceFilter.Text
        )
    ) 
)

That formula would be functional without delegation issues.

So, before we dive into other concepts, do you have such a potential narrowing pre-filter?

Also, what is the Items property of DelType?

[iwonder]

Hi Randy,

Thanks again for your help. I really appreciate it!

DelType Items: ["Small Bulk Load","Large Bulk Load","Belly Dump","Bag Truck","Poultry"]

I don't think there is a pre filter. DelType represents the column list of the SP List.

Once the DelType is set, the user can filter for a specific price in that column by entering a value in PriceFilter. The Title column should be unique per Price, but that is why I want to do a bulk upload, so that the user doesn't have to edit each Title value separately.

I mentioned the comma error just in case someone found this thread and pasted it in to their app and it didn't work. I hope you're ok with that.

Thanks

[RandyHayes · Accepted solution]

@iwonder 

Okay, then we can use the criteria as a pre-filter and see if that gets you to under 2000.

With({preFilter: 
    Filter(ProspectFreightRates, BagTruck = PriceFilter.Text || SmallLoad = PriceFilter.Text || BellyDump = PriceFilter.Text || FullHalfLoads = PriceFilter.Text)
     },

    Filter(preFilter, 
        Switch(DelType.Selected.Value,
           "Bag Truck", BagTruck = PriceFilter.Text,
           "Small Bulk Load", SmallLoad = PriceFilter.Text,
           "Belly Dump", BellyDump = PriceFilter.Text,
           "Large Bulk Load", FullHalfLoads = PriceFilter.Text
        )
    ) 
)

This will first filter the list based on the text entered in the PriceFilter control (by the way, are we dealing with text on the columns - BagTruck is a text column, etc.?)

Then based on that, we filter further based on the DelType with the Switch.

See where we get with that.

[iwonder]

Thanks Randy, that works!

Now to see if I understand.

The With() function makes a local variable which holds the filtered values? The filter works on all the table columns to find records that are equal to Value(PriceFilter.Text)? Once those are found, the rest of the With() is filtering the local variable and applies that recordset to the Gallery?

Thanks!

[RandyHayes]

@iwonder 

Yes, that is exactly right!