Couple of questions regarding REF, tables and Expressions in reporting via email

[justin2]

I’ve a report to send out via email every month from the records of 2 tables.

Record A is recorded everyday and report will be generated end of the month. I need to get the final record of the month subtract the final record from previous month. So I reference both tables using mm/yyyy and pre-recorded the mm/yyyy in the excel to populate the months for the whole year (Jan 2020, Feb 2020,…) into Record table. So when there is new record added to the Record A table, the record will also appeared on Report table according to the month.
Question: How do I hide or show some columns that reference to Report table? The thing is Record A table is also reference to another table and have to show all columns.

Record B is not recorded everyday but as and when is needed. A report will also be generated monthly together with Record A.

Question: How do I reference Record table B to Report table where the output is to select the last record (Date) and last completed record from the Record B? If I use mm/yyyy same as Record A as Ref, then only record for that month will show and the past records will not show. The records in Record B table can be many years ago eg. Jan 1999, Dec 2019, etc.

[Aleksi]

Hi Justin

Q1 - The easiest workaround is if you create a slice from that table A where you can select columns you want to show column by column. Then you need to create a view for that slice.
Q2 - So… you are interested to show only the latest record from table B? Do you need to show it in the app as well or just in the report?

[justin2]

Q1- Got it. Then I just need to reference the Report table to the slice (REF_ROWS(“Record A table - slice”, “key column”)).
Q2- The Record B table has the columns of the latest records to be filled by user. So, the records will show in the app. For the report, I guess I can just create virtual columns in Report table and pull the record/data from Record B table. To do that, I need to reference it to Report table. How do I ref it so that the past records of Record B table can be shown in every selected month in the Report row (April 2020, May 2020, June 2020, etc)?

[Aleksi]

Q1 - If you don’t want to show all columns in the related inline view, but you want to show all columns somewhere else you can just select columns in that system generated inline view and slice is not needed
Q2 - What is the relationship between Report B and Report tables?

[justin2]

Q2. I’m not sure but I think should be many-to-many relationship.

Below is a screenshot of Report table in excel sheet.

You see, everyday a new record will be created in Record A and will be referenced to the month column in Report table. So, when user click on the Mar 2020, all the Mar 2020 records will be shown in Report table, whereas for Record B table, the mm/yyyy can be a year ago (eg. Jan 2015). How to work this out in such a way that even user click on Mar 2020, the Record B of past years will still be still shown in the Report together with Record A? I can think of a way to reference both tables in order to write an expression and select the latest record in Record B which do not fall in Mar 2020 in the formula.

[Aleksi]

When you talk about Record B, do you want to show all records where the date is less or equeal than Jul 2020? Meaning… you want to show all records before 7/31/2020 which is the end of Jul 2020.

[justin2]

Hi Aleksi,
I did a mock up to show you to better understand as I’m quite bad in explaining especially English is not my first language.

If I use Date or UniqueID() for the ID and ref to Report Table, it will only show records for that month. I tried another method by keying in a random number in the excel for the Report table ID column and then use the same random number on “initial value” field in Record B table. So, when a new record being created, the random number will ref to the same random number in Report table. Almost exactly what I did for Record A using mm/yyyy. However, I got this error message during syncing “Value ‘123456789abc’ processing complete in field ‘1234567890abc’ cannot be converted to type ‘Ref’”.

[Aleksi]

If that’s the case and I have understood your request, you don’t need any references… you can write it with a SELECT() expression like SELECT(Record B[Key Column],TRUE). If you use virtual list column for that purpose, it will generate the same kind of inline view.

[justin2]

Well, that’s magic!
Come to think of it, I don’t really need to show the tables for Record A & B. Instead of using SELECT() to generate the same kind of inline view, is it possible to SELECT the latest record (MAX()) from the Record B table using virtual column in Report table? Similarly, can I create virtual columns in Report table and do the calculation of the monthly output (this month final record subtract last month final record) by SELECT the final record of the month without REF of inline view to the Record A table? So, the Report table only shows the required records for the monthly report without having to show the full records from Record A & B tables by REF.

[Aleksi]

You could use for example… LIST(MAXROW(“Record B”,"_rownumber")) with the virtual list column.

[justin2]

Using LIST() or REF() will create an Inline view for the table under Report. What I mean is that adding the records in the Report table using virtual columns eg. NUMBER (), TEXT() without showing Inline view. Can I use formula like NUMBER(LIST(… something like that in virtual column? How to write the formula?

[justin2]

I found that I can actually hide the List() columns and thus, hide the inline view. Then I use MAX([Related TableBs][_RowNumber]) to select the records I want to display in Report table. Is this the only way to do it?

[Aleksi]

And yes, you can generate those formulas directly on a template if you don’t need to show them in the app.

[Aleksi]

Yes it’s possible. Please specify what do you want to calculate with this “this month final record subtract last month final record”.

[justin2]

Basically, I want to calculate the output of the month. First, I need to take the last day of the month record (eg. Feb 28) and subtract the record from previous last record which is the last day of previous month (eg. Jan 31).

Without showing the inline view of Record A, how to write the formula to extract record and calculate the record from Record A?

[Aleksi]

When you have that related virtual list already, you can check the latest record’s key value like…
INDEX(ORDERBY([Related TableB],[_ROWNUMBER]),1). This you can then use in your template like [VirtualRefColumn].[Value].