Combining two sheets in a report

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?


4 Likes

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?

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.

2 Likes

image

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.

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.

@Pratyay_Rakshit

2 Likes

Then this happens.

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

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.

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.

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.

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"
)

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

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.

1 Like