Re: Combine two SELECT() expressions to form one?

hshelio · 08-23-2023 10:41 AM

Im trying to generate a type of account statement that has withdrawals and deposits listed in a table similar to a bank statement.

The withdrawals are in one table and the deposits are in another table.

How can i combine both SELECT() expressions into one?

The main goal is to use this in a template via the <<Start: SELECT() >> expression to create a PDF.

Marc_Dillon

You kind of can't. I recommend combining your withdrawal and deposit transactions into a single transaction table.

hshelio

That "Kind of cant" , leaves the door open or a workaround maybe?

The problem is that i have several tables that record diffrent types of costs/withdrawals (same thing) depending on which department is doing them. Such as maintenance cost, Employee Salaries, fixed costs, etc.

I keep them seprate for filtering purposes, and if i put them in a single table they will easily reach over 10s of thousands records or more by the end of the year.

Marc_Dillon

@hshelio wrote:

That "Kind of cant" , leaves the door open or a workaround maybe?

Maybe, but you didn't really give me much info to give a workaround solution. If you're wanting to display them in the same table in a template, one after another, that's easy. But if you need them interspersed, where like some withdrawals may be between some deposits or vice-versa, all ordered by date or datetime, that'd be hard.

Again, really, the same types of records should be in the same table. Devise other methods for filtering them if need-be. You know about Security Filters and Slices?

hshelio

" But if you need them interspersed, where like some withdrawals may be between some deposits or vice-versa, all ordered by date or datetime, that'd be hard. "

Yes, unfortunetly this is exactly what i need to do. Ordered by date.

I havent heard about security filters, but security isnt a priority at the moment, since the data is not sensitive.

I do know what slices are am using several to represent relative data.

However i cant refrence two seprate tables in a single slice, or so im told.

I guess a possible workaround would be to have a placeholder table that i can then fill out with Actions. But maybe that would not work because i would need to have a placeholder table for every user that wants to fill the table based on their own criteria. so i guess that wouldnt be very efficient..

Im sure there must be a way to do this, it seems like a logical use for this software to create bank statement like tables ( date, withdrawals ,deposits, balance)

not looking good for me 😞

Marc_Dillon

Security Filters aren't necessarily about security. More about determining which records to load into the app.

For your template, you could potentially iterate over a date table, pulling all records from each table per date. They'd still be one after another in their date blocks. It'd be like:

<<START: dates[id]>>
<<START: FILTER(withdrawals , [date] = [_THISROW-1].[date] )>>...<<END>>
<<START: FILTER(deposits, [date] = [_THISROW-1].[date] )>>...<<END>>
<<END>>

hshelio

I currently have it set up like this, but would work better mixed ordered by date. Ill keep investigating.

Marc_Dillon

Is it GSheet based? To get more granular, how I think you're wanting, and assuming you have a datetime column in both, change the above "dates" table to be a table with a formula on the spreadsheet like =SORT(UNIQUE({credits!B:B;debits!B:B})) assuming the datetime is column B in both sheets.

Denny774

@hshelio wrote:
Ordered by date

Hey man,

can't you do this?

LIST(
  ORDERBY(
    FILTER("withdrawals", TRUE),
    [date]
  ) + ORDERBY(
    FILTER("deposits", TRUE),
    [date]
  )
)

Marc_Dillon

A single START expression can't iterate over records from more than one table.

Even if it could , all this would do is stack all of the withdrawals on top of all of the deposits, each block ordered by date, but not interspersed by date like OP wants.