Referencing a column

Orion_Szathmary · 03-06-2021 10:40 AM

I have a table with columns named [Deposit 1], [Deposit 2], … , [Deposit 9].

The number of Deposits will vary in each row i.e., some rows will only have Deposits 1-3, others will have 1-6.

I also have a column named [Number of Deposits] where the user tells me how many deposits the row will have i.e., 5.

Is there a way to create an expression that dynamically references the LAST deposit… basically, I want to create an expression that dynamically references the last deposit, [Deposit [Number of Deposits]] ← if the [Number of Deposits] was 6, this would become a reference to [Deposit 6], if the [Number of Deposits] was 8, this would become a reference to [Deposit 8] and so on…

Thanks

PowerSheetAcade

Hi Orion.

I am thinking about a “switch” expression like the following:

SWITCH([NUMBER OF DEPOSITS], 1, [DEPOSIT 1], 2, [DEPOSIT 2], 3, [DEPOSIT 3], 4, [DEPOSIT 4], 5, [DEPOSIT 5], 6, [DEPOSIT 6], 7, [DEPOSIT 7], 8, [DEPOSIT 8], 9, [DEPOSIT 9], “”)

App Formula|690x269

In this case I’m using it into a virtual column to show the last deposit depending on the column [NUMBER OF DEPOSITS].

Virutual Column|690x331

Hope it results useful for you!!

AlexM

hi,
You shouldn’t have a table with columns deposit 1-x.
It’s a better practice to have a table called Deposits and every deposit would be a row in that table.
If one day you’ll have a deposit 10, you don’t want to go and add another column.

If you tell us a bit more about the app, there is a way to get there.

Orion_Szathmary

It’s a real estate app for pre-construction condos. Each deal has several deposits associated with it and need to be mapped back to the deal. Is a deposit table still the right approach, structurally.

AlexM

Deals table : ID, DealName, and so on
Deposits table: ID, Deal_ID (ref to deals table), DepositDate,DepositAmount

This structure means that every row in deposits table will represent a deposit rather than all the deposits for a deal.
By having the column Deal_ID ref to Deals table, that means that that deposit is assigned to a specific deal.

COUNTING THE NUMBER OF DEPOSITS
In the Deals table, you can add a virtual column (your Number of Deposits - but virtual column) with the formula
COUNT(SELECT(Deposits[ID],[_THISROW].[ID]=[Deal_ID]))
this means: go to Deposits table and count all the deposits that have the column Deal_ID equal with the ID column of this deal.

Then you go to Deposits Inline view and sort descending by [DateColumn] (I am assuming you have a date column)

When you will open Deals Details view in your app, you will have a small table (called nested table) with all your deposits for that deal, with the last deposit being first in the list. That’s why you did that sorting earlier.

In the settings of Deal Details view, you will see

This is set by default to 5.

This means how many rows you will see in that small table on the Deals Details view.

Under the nested table you will have 2 “buttons”, View and Add
View - opens the deposits page and will only have the deposits for that deal
Add - opens a form to add a new deposit for that deal.

I hope it is what you are looking for and it helps.

Steve

Try:

ANY(
  LIST(
    [Deposit 9],
    [Deposit 8],
    ...,
    [Deposit 1]
  )
  - LIST("")
)