href to goto to account?
Moderator: Renato
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
href to goto to account?
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.
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.
-
- 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?
I've updated the report
http://forum.moneymanagerex.org/viewtop ... 388#p12388
Now Lua script does not used for hyperlinks.
Where 'Nr' is "checkingaccount_v1.transid AS Nr" in SQL script.
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>
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
Can i do it without a transid though? I can surely do it with some wasteful SQL, but it isn't efficient.
-
- 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?
Transid should be here.
I don't understand what the problem to use it. Any transaction has unique id.
I don't understand what the problem to use it. Any transaction has unique id.
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
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.
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
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
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?
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')));
Is there a way to go to the destination account?
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
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 .
-
- 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?
You have not provided SQL script. Any discussion has no sense.
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
As a programmer by profession it is definitely possible to discuss code without see it, but if you insist.
sql
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;
-
- 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?
Now I understand. In that case it maybe added more hyperlink types for account id.
But your solution is smart.
But your solution is smart.
-
- 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?
I suggest this report for your purposes.
You do not have the required permissions to view the files attached to this post.
-
- MVP User
- Posts: 674
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: href to goto to account?
Hello Nikolay
Good job, but I miss the total of monthly columns
Good job, but I miss the total of monthly columns
Renato Forum Administrator
-
- 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?
I'll add links for all cells, translations and totals in next release. And dorting as well.
-
- 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?
Account balances to end of month for last 12 months
You do not have the required permissions to view the files attached to this post.
-
- 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?
Here is the same report with graph:
http://forum.moneymanagerex.org/viewtop ... =16&t=5915
http://forum.moneymanagerex.org/viewtop ... =16&t=5915
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
I'll upload my full report when I get home. I'm out of the country at the minute. It has totals, and also change per month.Renato wrote:Hello Nikolay
Good job, but I miss the total of monthly columns
-
- Super User
- Posts: 72
- Joined: Thu Dec 11, 2014 8:40 am
- Are you a spam bot?: No
Re: href to goto to account?
I've uploaded my latest report to the thread linked here
Its is more relevant than this support thread.Nikolay wrote:Here is the same report with graph:
http://forum.moneymanagerex.org/viewtop ... =16&t=5915