Pull Records in Separate Tables For Report

[kpcollier]

I have two tables that share a lot of common fields. I am trying to create a Report that searches by Job and returns results from both tables. Essentially, I want all Order Records from both tables based on what Job I search by. 

I created a view with the Orders_Table and Completed_Table. However, this joins records together. I need them each to be separate records. In a view, I need to join records somehow, so I'm having trouble just getting the data from the two tables to make a report together.

Here is a screenshot of what I've got with the view I created and adding the fields from both tables into the report. They need to be split at the red line.

[DefinitelyNot31337]

hi @kpcollier,

 

 

I believe what you want is a UNION of both tables, and NOT a VIEW. Here's a good article differentiating the two:

https://www.essentialsql.com/what-is-the-difference-between-a-join-and-a-union/

 

Unfortunately, Caspio does not have a direct table operation that allows UNION of both tables. The closest we can do is to create another table that has the common fields of your first two tables. We can, then populate the new table with Triggered Actions or Tasks to yield your desired result. After, then, just use the new table as your DataSource.

 

I hope this helps.

 

DN31337

[Vitalikssssss]

Hi @kpcollier,

What type of Join are you using in the View?

I can suggest adding a field with a default value "0" to Orders_Table and field with a default value "1" to Completed_Table. 

You should use newly added fields in Join settings of the View and also select ALL records from both tables.

Hope this helps.

Regards,

vitaliksssss

[kpcollier]

Ahh, that makes sense. Really sucks that we can't use it here! Janky workarounds, here I come.