Combining two sheets in a report

Pratyay_Rakshit · 07-12-2020 06:53 AM

I want to combine two sheets in a report.

Say the report has 5 columns.

The first 4 columns would be from sheet 1, of which the 1st column would be a date column and the rest 3 aren’t of concern.

The 5th column along with the 1st column, would be from sheet 2, from which the 1st column would be a date column as well.

I hope I’m making sense.

How would I combine the two sheets in the report?

LeventK

Pratyay_Rakshit

I haven’t tested this out yet.

Still I need to know a thing before I do that, and since it is pertinent to my problem I didn’t want to create another thread for a one reply discussion.

Can the [_THISROW] expressions be used within report templates? In a particular sheet there are records for all entities, and I want to generate reports from that sheet for particular entities the action for which would be available from the entity profile page.

If I just wanted to view the records from within the app, I would use something like linktofilteredview(“View Name”, [Name]=[_THISROW].[Entity Name]). Can the same expression be used within the report template?

LeventK

If your looking up value from another table that you are originally initializing the query, you need to use [_THISROW]. You can use it in the workflow template for sure if you will be pulling out data from another table other than the originating table of the workflow.

Pratyay_Rakshit

This is the main body of the template.

This is the error expression when I test it.

When the END in the second start expression is not given, then the Unable to find function START : SELECT … thing comes still.

Pratyay_Rakshit

Oh maybe its because I have included them in continuous cells. Let me check and try it again.

Re: Well the errors are now eradicated, but there’s one strange problem.

If this is the position of the second end expression, then the report comes through as

If this is the position of the second end expression, then the report comes through as :

Also it seems that the Date and the credit columns will not be matched, as the Date column is linked primarily to the sheet that contains the debit column.

LeventK

@Pratyay_Rakshit

Pratyay_Rakshit

Then this happens.

LeventK

Why don’t you combine/include that [Credit] column with your main table and apply an expression like:

LOOKUP(
	[_THISROW].[Key],
	"RMC Payments",
	"RefColumnName",
	"Amount"
)

Assuming that there shall be a REFERENCE between your RMC Bill entry table and RMC Payments table

Pratyay_Rakshit

In the bare minimum, taking only the pertinent columns, the table RMC Bill entry contains a [Date] column, a [Company] column and a [Debit amount] column.

The table RMC payments contain a similar [Date], [Company] and a [Credit amount] column

The common one is the [Company] column.

How am I going to do it, the way you are telling me to do it? Give me just a little rope.

Pratyay_Rakshit

I modified it a bit and hoped that it would work, because the two start-end expressions seemed to have worked correctly when used separate, giving the right outputs in the desired format.

But the thing didn’t work when put together. I got an error message of an unmatched end.

Pratyay_Rakshit

The only option that might have been left now is to combine the two tables as you were suggesting. I’m not sure how I would do that because the two tables capture very fundamentally different data.

LeventK

If your [Company] column is the common column in both RMC Payments and RMC Bill entry, then a simple LOOKUP should solve the issue:

LOOKUP(
	[_THISROW].[Company],
	"RMC Payments",
	"Company",
	"Amount"
)

Pratyay_Rakshit

And this lookup column will be placed on the RMC Bill Entry sheet?

LeventK

I mean your template. Combine the 2 tables in your template and put the expression in the last column and place the <<End>> to finalize.