href to goto to account?

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

href to goto to account?

Post by sterling »

Hi,

I'm using Nikolay's dolls report as reference here, and i want to make a URL link to an account in one of my reports.  But i'm unsure how to do this.  The doll example uses a transaction id, but i dont have a transaction id from my SQL.  Is there a way to link to an account without a transaction id?

Here is what i've got as reference
record:set("TRX_GOTO", string.format('<a href="trxid:%s">%s</a>', record:get('Nr'),  record:get('ACCOUNTNAME')));

Thanks.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: href to goto to account?

Post by Nikolay »

I've updated the report
http://forum.moneymanagerex.org/viewtop ... 388#p12388

Now Lua script does not used for hyperlinks.

Code: Select all

        <td class = "text-right"><a href='trx:<TMPL_VAR Nr>' ><TMPL_VAR Nr></a></td>
        <td nowrap><a href='trxid:<TMPL_VAR Nr>' ><TMPL_VAR ACCOUNTNAME></a></td>
Where 'Nr' is "checkingaccount_v1.transid AS Nr" in SQL script.
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: href to goto to account?

Post by sterling »

Can i do it without a transid though?  I can surely do it with some wasteful SQL, but it isn't efficient.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: href to goto to account?

Post by Nikolay »

Transid should be here.
I don't understand what the problem to use it. Any transaction has unique id.
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: href to goto to account?

Post by sterling »

This is to link to accounts where my report doesn't have transactions.  Like I say I can add transactions to the report but it's not efficient.
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: href to goto to account?

Post by sterling »

This is a report to show all my accounts and savings levels.

I've extended my sql now to have a transid in my list of accounts, but have stumbled on a different issue now and wondered if there was a solution.  Here is my lua

Code: Select all

record:set("TRX_GOTO", string.format('<a href="trxid:%d">%s</a>', record:get('A_Transaction'), record:get('ACCOUNTNAME')));
But if its an account with only transfers to the account, i get one of those transactions, but this hyperlink takes you to the source account, not the destination account.

Is there a way to go to the destination account?
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: href to goto to account?

Post by sterling »

Ive found a hacky work around.  I've created void  transfers from accounts which dont have any already.  Now all my accounts can have links :lol:.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: href to goto to account?

Post by Nikolay »

You have not provided SQL script. Any discussion has no sense.
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: href to goto to account?

Post by sterling »

As a programmer by profession it is definitely possible to discuss code without see it, but if you insist.

sql

Code: Select all

select a.ACCOUNTNAME,
   strftime('%m-%Y', date('now', 'start of month', '-11 month')) as date11,
   strftime('%m-%Y', date('now', 'start of month', '-10 month')) as date10,
   strftime('%m-%Y', date('now', 'start of month', '-9 month')) as date9,
   strftime('%m-%Y', date('now', 'start of month', '-8 month')) as date8,
   strftime('%m-%Y', date('now', 'start of month', '-7 month')) as date7,
   strftime('%m-%Y', date('now', 'start of month', '-6 month')) as date6,
   strftime('%m-%Y', date('now', 'start of month', '-5 month')) as date5,
   strftime('%m-%Y', date('now', 'start of month', '-4 month')) as date4,
   strftime('%m-%Y', date('now', 'start of month', '-3 month')) as date3,
   strftime('%m-%Y', date('now', 'start of month', '-2 month')) as date2,
   strftime('%m-%Y', date('now', 'start of month', '-1 month')) as date1,
   strftime('%m-%Y', date('now')) as date0,

   (select max(t.TRANSID)
      from CHECKINGACCOUNT_V1 as t
      where  t.ACCOUNTID = a.ACCOUNTID or t.TOACCOUNTID = a.ACCOUNTID
   ) as A_Transaction,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-11 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-11 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance12ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-10 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-10 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance11ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-9 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-9 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance10ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-8 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-8 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance9ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-7 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-7 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance8ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-6 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-6 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance7ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-5 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-5 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance6ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-4 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-4 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance5ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-3 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-3 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance4ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-2 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-2 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance3ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-1 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-1 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance2ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         where  TRANSDATE < date('now', 'start of month', '-0 month')
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer' and TRANSDATE < date('now', 'start of month', '-0 month')
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R'
   ) as Balance1ago,

   (select a.INITIALBAL + total(t.TRANSAMOUNT)
      from
      (select ACCOUNTID, STATUS,
         (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
         from CHECKINGACCOUNT_V1
         union all
         select TOACCOUNTID, STATUS, TOTRANSAMOUNT 
         from CHECKINGACCOUNT_V1
         where TRANSCODE = 'Transfer'
      ) as t
      where  t.ACCOUNTID = a.ACCOUNTID and t.STATUS == 'R' 
   ) as BalanceNow

from ACCOUNTLIST_V1 as a
where a.STATUS = 'Open' and a.FAVORITEACCT <> 'blahTRUE'  and a.ACCOUNTTYPE!='Credit Card' and a.ACCOUNTNAME<>'Santander-Joint' and BalanceNow>0
group by a.ACCOUNTNAME
order by BalanceNow desc
limit 20;
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: href to goto to account?

Post by Nikolay »

Now I understand. In that case it maybe added more hyperlink types for account id.
But your solution is smart.
Post Reply