Jump to content
  • 0
Sign in to follow this  
Colnocode

Report problem - How to?

Question

I am trying to produce a report that is 101 with a spreadsheet but can’t figure out how to do it with Caspio. 

Very simplified so I can explain my difficulty. I have two tables  <Income> and <Cost>. Each table contain the following fields:-  ID; Project#;  and Amount.

My desired report something like this

Project#  ~ Income_Amount ~ Cost_Amount

1                           $200     

1                           $250

1                                                       $150

1                                                       $250

Total Project      $450            $400     Profit $50

I tried to use a View to get the <Income> & <Cost> by project , However, I  wound up  hundreds of records - seemed to combine every <Income> with every<Cost>. And the report was meaningless.

Simple task but how?

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

 Hi @Colnocode,

 

May I ask why do you need to have a separate tables for Income and Cost? I tried to create a View that joined two tables, however, the result is not like what you want.

So I just thought of creating a third table that contains project#,  income and cost fields. I also have a triggered action for the two tables that will insert a record to the third table.

And I used the third table as the Data Source of my report. 

 

Please see screenshots below.

image.png.431974c61935d1aaf96a4512dcec1746.png

 

image.png.eb36715dc2046212483085d6bac99e09.png

 

Here is the output.

image.thumb.png.e565485ecf2f1aa4cf1e05b4fd78f51e.png

 

Hope this help. 

 

-kristina

Share this post


Link to post
Share on other sites
  • 0


FHi @Colnocode

You can actually just add a calculated field in your report DataPage and used the sample SQL code to get the value of the 'Cost_Amount' field from your 'Cost' table.

SELECT Cost_Amount
FROM Cost
WHERE cost_ID =target.[@field:cost_ID]

For this solution to work, you will need to ensure that you have set up a relationship between your  'Income' and 'Cost' table. There should be a common field between this two tables, and in my example, I've added the 'cost_ID' (which is the primary key of my cost table) to my   'Income' table in which will serve as a common field between two tables (foreign Key) to link them.

Here is the view of the relationship that I've to establish for this solution to work 

image.png.f375eba15a399d349caeb0ca2a59ae20.png

For more details about  table relationships, you can review this link 

For more details about the calculated field, you can review this link  

I hope this helps.

Regards,

TsiBiRu

Share this post


Link to post
Share on other sites
  • 0

Hi Kristina,

 

Thanks for your input. As I mentioned the example the <income> and <cost> are super simplified, actually they are part of a quite complex live system that I want to expand. The issue I see with your solution it does not readily allow for the dynamic selection of a project or projects to report on??

Thanks though it is a possible solution

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...