Return ID depending of variables

[DoDoTwin]

Hello.

I have 2 tables: "Property" and "Lease".

1 property can have N leases.

1 lease have a Start and End dates and also a lease amount.

I woud like to return the current lease (Lease ID and the lease amount)

For example, there are 2 leases:

(1) Jan 1st 2012 to December 31st 2014 for $1000 per month

(2) Jan 1st 2015 to December 31st 2017 for $1100 per month

Today is November 9th 2016. So at the date of today the lease to return is lease #2 and $1100 for the lease amount.

How is it possible to return a row and some data depending of criteria? Datapages? Views? Pivot tables?

 

Thanks

Dorian

[MayMusic]

You can have SELECT Statement for instance:

 

SELECT top 1 LeaseAmount FROM Lease_tbl WHERE Lease_ID = target.[@field:LeaseID] AND Date < LeaseEndDate

[DoDoTwin]

Hi MayMusic,

Thanks for your answer.

Where in the Datapages can I add the SQL syntax.

I haven't found yet anything in the Caspio Documentation about that.

Once I know where to add the sql syntax, it should be easy.

Thanks

Dorian

 

 

[DoDoTwin]

Also, If I have 1000 properties, I would like to run this SQL syntax for each row and to save the RENT amount in the property table.

 

[Ashfak]

In calculated field