How to delete a specific value from a list?

[Fran_Vidicek]

Here is my doc with the problem.

The problem is probably withing the Submit button’s formula. To be precise the Splice() function.

The formula is supposed to filter out currencies (*Note: Currencies table is not visible in the gif.) that belong to the same country that is being edited, but are not in the list of Select Countries.

And then for each of those currencies under their Country column find the Country being edited and delete it.

Result: Successfully removed currency from the country.

[Pch]

Argh … I’ll be back

[Piet_Strydom]

HI Fran,

Why not simply modify the currency table, rather than the country table?

So you know there is a problem with the Euro currency, open that row in the currency table, and modify it.

Rambling Pete

[Fran_Vidicek]

This doc is just a small snippet of another doc. In the main doc there are more functionalities and that workaround wouldn’t work. I don’t the countries table to be directly editable. That’s why I use the Edit button.

[Piet_Strydom]

The idea is still that you use a button, but use the modifyrow() formula on the currency table.

[Fran_Vidicek]

So for each currency (row) that belongs to [Country being edited] and is not a part of [Select currencies], I should modify their [Country] column and put what value in there?

The value should be all of the same countries without the [Country being edited], but I don’t know how to do that. I thought about doing that using the ListCombine() formula but I can’t figure out how to select all countries but this one.

CurrentValue
.ListCombine(
[Currencies].[Country],
[Edit table].[Country being edited].Not()
)

Idk if this formula is right but, you get the point… I don’t know how to pick all but one.

[Pch]

Ok so, I feel like my Submit button is a bit clunky but here it goes :
(See button [Submit edits - 2] in the sample below )

Currencies.Filter(
  Country.Contains(thisRow.[Country being edited])
  AND CurrentValue.Contains(thisRow.[Select currencies]).Not()
).First().WithName(C, 
  C.ModifyRows(
    Country,
    Splice(
      C.Country,
      C.Country.Find(thisRow.[Country being edited]),
      1
    )
  )
)

So, the Filter() part

Currencies.Filter(
  Country.Contains(thisRow.[Country being edited])
  AND CurrentValue.Contains(thisRow.[Select currencies]).Not()
).First()

returns the row from the table Currencies for which the Lookup Country should see its actual value modified …

This row is then stored within C using WithName() …

[...].WithName(C, 
  C.ModifyRows(
    Country,
    Splice(
      C.Country,
      C.Country.Find(thisRow.[Country being edited]),
      1
    )
  )
)

… And for C I ask the button to modify the column Country with the value :

Splice(
      C.Country,
      C.Country.Find(thisRow.[Country being edited]),
      1
    )

The value C.Country in Splice() is the list of countries in the lookup Country for the row actually stored in C.

E.g.: If the row returned by the Filter() is Euro, C.Country would be in your doc [Slovenia,Croatia,France,China]

The start of the Splice() is the position of the country selected in thisRow.[Country being edited] has in is the list of countries in the lookup Country for the row actually stored in C ( )

E.g.: If the row returned by the Filter() is Euro and thisRow.[Country being edited] is China, C.Country.Find(thisRow.[Country being edited]) would return 4

And the delete count for Splice() is 1

Now, to be honest, I’m not 100% behind that formula (I can’t find what but something is bothering me a little )… even though, after testing it, it seems to do its job

But, I still added another button [Submit edits - 3] which does the same thing but instead of having everything calculated within the button, it simply uses the fields next to it (which can be hidden)

So the fields :

• [Currency to modify] is the row which should be modified in the table Currencies
• [Current countries] is the list of countries in the Lookup Country (still in the table Currencies)
• [Position - Country being edited] is the position of the country selected in thisRow.[Country being edited] in thisRow.[Current countries]
• [Country Should become] is the spliced list of [Current countries] which is used to modify the appropriate row in the table Currencies

This is just an idea

And, to illustrate what @Piet_Strydom suggested, I’ve created another example but the button will modify the lookup in the table [Countries 2] (instead of [Currencies 2])…

To still use the button to modify the lookup while also making that lookup not easy to edit manually, I’ve added a text field called Currency which simply gather the values in the lookup [Currency (To Hide)]…

If you hide the lookup and only display the text field, you can prevent the lookup to be manually edited (Locking might help there too)

The button [Submit edits] has been modified accordingly to that setup

Hope this helps

[Pch]

Small add-on (again … Sorry @Fran_Vidicek ) because I inadvertently changed the initial workflow in my previous suggestion …

In my previous reply, when wanting to edit the currency for a country, one would need to select the correct currency in the helper table : Press 'Edit Country' button above ↑ to select a country and not the one to remove …

This has now been corrected (I.e.: One now need to choose the currency to remove instead of the right currency in the lookup [Select currencies] in the helper table )

It stills follows the same principles as in my previous reply though but I modified the filter a little bit …

So the formula action in the button [Submit edits - 2] is now :

Currencies.Filter(Currency.Contains(thisRow.[Select currencies].Currency)
).First().WithName(C, 
  C.ModifyRows(
    Country,
     Splice(
      C.Country,
      C.Country.Find(thisRow.[Country being edited]),
      1
    )
  )
)

Sorry that I didn’t see my mistake before posting … But I still hope it will help a little

[Fran_Vidicek]

Thank you!

The first button wasn’t a good option because it wouldn’t work if I wanted to remove more than one currency, but the second doc solved all my problems.

Thank you once again, it’s really helpful to get someone else’s mind on the problem because you for example approached the problem from completely different angle and who knows how long it would take me to even think about it, and at the end it was a simple solution.

[Pch]

No problem @Fran_Vidicek !

I’m very glad to know you were able to make it work !

@Fran_Vidicek

the second doc solved all my problems.

It was the very first thing I tried before saying that I was getting back
(as it seemed like a shorter and easy way to get to a possible solution)

@Fran_Vidicek

The first button wasn’t a good option because it wouldn’t work if I wanted to remove more than one currency

I’ll admit that this thought crossed my mind but I wasn’t sure you would need it (and I then got distracted IRL and forgot to test this as well… Sorry about that ) .

So, just for completeness (in case someone else would need something similar), here’s a button allowing you to remove multiple erroneous currencies for a country (See [Submit edits - 2] in the sample)

The workflow is :

1. Click on the Edit Country button in the table Country for the country having erroneous currencies tied to it.
   The button will add a row in the helper table below
2. In the helper table, select the currencies you want to remove for the selected country (leaving out the correct one)
3. Click on Submit ([Submit edits - 2] or [Submit edits - 3])

The whole Action Formula in [Submit edits - 2] is :

Currencies.Filter(
  Currency.Contains(thisRow.[Select currencies - The ones to remove].Currency)
  ).ForEach(
    RunActions(
      ModifyRows(
        CurrentValue,
        Country,
        Splice(
          CurrentValue.Country,
          CurrentValue.Country.Find(thisRow.[Country being edited]),
          1
        )
      )
    )
  )

And similarly to my previous reply, what it does is :

Step 1 : Filter()

Currencies.Filter(
  Currency.Contains(thisRow.[Select currencies - The ones to remove].Currency)
  )
[...]

So we take the table Currencies and we ask the filter to look for and keep the rows where CurrentValue.Currency contains the Currency of thisRow.[Select currencies - The ones to remove] (i.e.: the erroneous currencies)

This returns a list of rows from the table Currencies

Now that we have the appropriate list of rows, the Step 2 is :

[...].ForEach(
    RunActions(
      ModifyRows(
        CurrentValue,
        Country,
        Splice(
          CurrentValue.Country,
          CurrentValue.Country.Find(thisRow.[Country being edited]),
          1
        )
      )
    )
  )

… For each row returned by the filter and stored within CurrentValue, ModifyRows() :

• The row to modify is CurrentValue

• The column is CurrentValue.Country
  I.e.: The field Country in the table Currencies for that specific row

• The columnValue is the spliced list of Countries for that specific row

  Splice() :

  • The value is CurrentValue.Country
    I.e.: The current list of countries in the field Country in the table Currencies for that specific row ( stored within CurrentValue)

  • the start is CurrentValue.Country.Find(thisRow.[Country being edited])
    I.e.: The position of thisRow.[Country being edited] in the current list of countries for that specific row

  • the deleteCount is still 1
    (For 1 currency in thisRow.[Select currencies - The ones to remove] we remove 1 country from thisRow.[Country being edited], 1 by 1 in this case )

And the button [Submit edits - 3] still uses the fields next to it to get to the same result