Alpha Video Training
Results 1 to 27 of 27

Thread: Totalling payments due vs. payments made

  1. #1
    Member
    Real Name
    Jeff Funk
    Join Date
    Jan 2011
    Posts
    22

    Default Totalling payments due vs. payments made

    I've got a table with customer info in it that's linked to a table of payments due on dates and payment amounts using a customer ID. I want to be able to calculate the total of the payments due and payments made before the current date to get an overdue amount and display that on the main grid. I'm at a loss as to how to do that and haven't been successful searching... anyone have any suggestions?

  2. #2
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    I've been looking to achieve something similar to this. If anyone knows a way of going about it it'd be a great help!

  3. #3
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,216

    Default Re: Totalling payments due vs. payments made

    If you're using SQL, do it in the a view...

  4. #4
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    Another way might be to use one of the Grid Client-side Events, such as onGridRender. In this event perform an Ajax Callback. This gives you access to the "e" object which contains a ton of information about the grid. The Ajax Callback, which is a function, can execute XBasic which can open your table and perform a select and total of the records you want. Once you've got your totals you can put the information into a section of your grid. Post some sample data to work with, whether it's DBF or SQL and an idea of the select statement that would get the information you need. From there we can figure out more of what needs to be done.

  5. #5
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,216

    Default Re: Totalling payments due vs. payments made

    This is like what you want to do:
    http://www.screencast.com/t/UlciEnldUn

  6. #6
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    Excellent sql views.

  7. #7
    Member
    Real Name
    Wendy Welton
    Join Date
    Feb 2011
    Location
    Hampton & North Hampton NH
    Posts
    809

    Default Re: Totalling payments due vs. payments made

    Quote Originally Posted by jjfunk View Post
    I've got a table with customer info in it that's linked to a table of payments due on dates and payment amounts using a customer ID. I want to be able to calculate the total of the payments due and payments made before the current date to get an overdue amount and display that on the main grid. I'm at a loss as to how to do that and haven't been successful searching... anyone have any suggestions?
    Does Lee's video get you over the hump?

    If it doesn't:

    1 - Are you DBF or DQL?
    2 - Where are you stuck? Are you stuck on doing a Grid that accesses both tables? Or on then doing the math?
    Wendy Welton
    Architect
    past & future Alphaholic - deliberately falling off the wagon!

    http://www.artformhomeplans.com/

  8. #8
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,216

    Default Re: Totalling payments due vs. payments made

    Here is an Aged-Invoice Grid video that might help.http://www.screencast.com/t/0YtxtAzbBp

  9. #9
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    I'm going to have a go implementing some of these methods today. Great to see so many responses!

  10. #10
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    I think the solution I'm looking for is a lot simpler. There's an example of what I'm trying to achieve in the first video.

    total_field.jpg

    Would this have been achieved using the SQL Views, or some XBasic function perhaps?

    Specifically, I need a total field that displays somewhere about a grid component showing the total number of holidays ("No Days") that a user has taken.
    A master table holds a number of records relating the year and the user, whilst a child table holds information on each holiday period booked and stores the number of days each time a record is added.
    The number of holidays allowed per year is also stored in the master table.

    holiday_grid.jpg

  11. #11
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Davidk,
    I've been trying to go about implementing your original suggestion (avoiding Views) but I'm missing some things.
    So far I've an ajaxCallback() on the onGridRender event which uses the XBasic function setTotal().

    The idea was to have setTotal() assign a value to a <span> tag in a Freeform edit region using e._setElement.spanid.value except that I've no idea how to query the database and get the values I need.
    I've been searching the forums/tutorials but I'm not exactly sure what I'm searching for so I've had no good results.

    The SQL statement should go something like:
    SELECT userID, Holidays(total) FROM sql_holidays
    WHERE userID = grid.userID


    PS!
    I was just looking at <TBL>.EXTERNAL_RECORD_CONTENT_GET() and <TBL>.FIELD_STATISTICS() whilst writing this and they seem like they should be able to achieve what I need.
    I'll have a go implementing them in the meantime and hopefully that will be that, but I'd appreciate any input either way.

    Thanks!!

  12. #12
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    I'm getting an error using <tbl>.field_statistics()
    Error executing Ajax callback function 'setTotal' Error reported was: command: tbl.field_statistics("NO_DAYS",hols) Not supported
    If it's not supported or I can't figure out what the issue is then I'm back to being stuck.

  13. #13
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    A callback would work nicely for this. The following code is lifted pretty much from Selwyn's videos... so all good stuff and nice error checking. You can add more error checking to the arguments as well just to make sure they get set properly. I put a DIV ID into the top part of my grid and the callback is setting the text for that. Hope it helps.

    Code:
    function countDetails as c (e as p)
    
    dim currentProdId as c 
    'note - since this grid is NOT editable, by default data from the current row is NOT submitted
    'when the callback is made (the Primary Key is submitted).
    'So, e._currentRowData will be empty, UNLESS you check the 'compute current row data' checkbox
    'when defining the ajax callback in action javascript
    currentProdId = e._currentRowDataNew.ProductId
    
    'ok, now that we have the value we want for the current row, let's do an AlphaDAO query to get 
    'the data we want
    dim cn as sql::connection
    dim cs as c 
    
    'get the connection string that is defined in this Grid component
    cs = e.tmpl.cs.connectionString	
    
    dim flag as l 
    flag = cn.open(cs)
    if flag = .f. then 
    	dim msg as c 
    	msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
    	'we need to encode this for javascript since the message will be sent back to the browser for display
    	'js_escape() encodes ' as \' and crlf as \n
    	msg = js_escape(msg)
    	dim jscmd as c 
    	'create the javascript command to send back to the browser. this command will
    	'display a javascript alert box.
    	'at this point we are done, so exit the function and return jscmd.
    	'note that in xbasic the way a function returns a value is you set the function name to the value
    	'you want to return.
    	jscmd = "alert('" + msg + "');"
    	countDetails = jscmd
    	exit function 
    end if 
    	
    
    dim sql as c 
    'note that the sql uses an argument in the where clause. arguments start with :
    sql = "select Sum(ProdDetailsId) as SumProd from tblProductDetails where ProductId = :getProdDet"
    dim args as sql::arguments
    'set the value of the 'whatState' argument. 
    args.add("getProdDet",currentProdId)
    
    flag = cn.Execute(sql,args)	
    if flag = .f. then 
    	'there was an error - close the connection and exit
    	cn.Close()
    	dim msg as c 
    	msg = "Could not execute the Sum query. Error reported was: " + cn.CallResult.text
    	msg = js_escape(msg)
    	dim jscmd as c 
    	jscmd = "alert('" + msg + "');"
    	countDetails = jscmd
    	exit function 
    end if 
    	
    
    dim rs as sql::resultset
    rs = cn.ResultSet
    dim recordCount as n 
    'since we executed a count query, the resultset will only have one column - which holds the count
    'read the data in the first (and only) column of the resultset
    recordCount = rs.data(1)
    
    dim jscmd as c 
    jscmd = "$('TotalCount').innerHTML = 'Total count for " + currentProdId + " is " + recordCount + "';"
    countDetails = jscmd
    
    end function

  14. #14
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Just a few things to ask about.

    *Since you're submitting to a non-editable grid, do you have the row property set to 'NONE' in the ajaxCallback() function?
    {Grid.Object}.ajaxCallback('G','NONE','countDetails')
    *Which event do you call the function in?

    *What is your :getProdDef set to? I'm not sure what that value is based on.

    *I'll be using this on 2 different grids, one editable and one non-editable. Will implementing it in this way (treating both as non-editable) create any issues that you can foresee?

    I'll work on implementing this now and making any changes I need to make. Should work perfectly.
    Thanks David!
    Last edited by RaEd.; 08-12-2011 at 07:39 AM.

  15. #15
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    The function is called from the Javascript System Events - onGridRender (sorry about that - kinda forgot to include that)

    Should have shown the ajax callback as well. I let A5 create these by starting with an Action on a click event of a field... just to get the syntax right. But, it has to be changed a little bit. The call is

    Code:
    {grid.Object}.ajaxCallback('G','{Grid.RowNumber}','countDetails','','_getData=true');
    On a non-editable grid you need to check the Compute Current Row Data of the ajax builder... this puts in the '_getData=true' section. However, '{Grid.RowNumber}' actually gets created as '{Grid.RowNumber}:all' which creates a problem, so get rid of the :all and "all" is well.

    With the callback set for a non-editable grid, it seems to be fine for an editable grid as well.

    :getProdDef is set in this line...

    Code:
    args.add("getProdDet",currentProdId)
    and currentProdId is set at the top of the function.

  16. #16
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    That's all great, I'm making some real progress now!

    I'll show you what I have so far.
    Since the grid I'm performing the callback on is a child, I get the relational key from the parent. If it gets that value I perform the callback and pass the key:
    Code:
    var po = {grid.Object}.getParentObject();
    if po(){
    	var holID = po.getValue('G', 'COMBO', po._selectedRow);
    	{grid.Object}.ajaxCallback('G','{Grid.RowNumber}','sumHols','','_getData=true&comboid='+holID);
    }
    My function, sumHols(), is pretty similar to the one you posted with a few changes to make it work for my purposes, but I'm still tweaking it right now. Hopefully will have it working in the next hour!

    Hopefully

  17. #17
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Are these errors informative at all? They always seem to put out the same message (unless I just keep making the same error!)
    err0r.jpg

    The function looks in order but these errors are popping up in threes;
    the one about the missing '(' and 2 about not being able to identify Grid1_GridObj. I assume the latter 2 are thrown up in response to the first error.

  18. #18
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    Unless someone here knows better, I always have such trouble with these errors... some of the time they're right... about missing a semi-colon etc. But other times who knows.

    is this the code giving the error?

    Code:
    var po = {grid.Object}.getParentObject();
    if po(){
    	var holID = po.getValue('G', 'COMBO', po._selectedRow);
    	{grid.Object}.ajaxCallback('G','{Grid.RowNumber}','sumHols','','_getData=true&comboid='+holID);
    }

  19. #19
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Ah, yes it is. The issue was the if statement; it should have been if (po){
    I was to busy scouring the function code to spot it!

  20. #20
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    More times than I care to admit it's a { or }. Coming from a bunch of basic languages I'm finally starting to know them as "then" and "end if". I put them in right away now so I don't miss them as much.

  21. #21
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Gonna drop this code on you for one last look (hopefully)

    I'm getting this error after publishing and displaying the grid online:
    Error executing Ajax callback function 'sumHols' Error reported was: command: holTotal = rs.data(1) Function sequence error. while getting result set data column.'
    Here's what I have at the moment
    Code:
    function sumHols as c (e as p)
    	dim curCombo as c 
    	'comboid passed via the callback
    	curCombo = e.comboid
    
    	dim cn as sql::connection
    	dim cs as c
    
    	cs = e.tmpl.cs.connectionString	
    
    	dim flag as l 
    	flag = cn.open(cs)
    	if flag = 0 then 
    		dim msg as c 
    		msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
    		msg = js_escape(msg)
    		dim jscmd as c 
    		jscmd = "alert('" + msg + "');"
    		sumHols = jscmd
    		exit function 
    	end if
    
    	dim sql as c 
    	sql = "select Sum(NO_DAYS) as TOTAL_DAYS from sql_holiday_detail where COMBO = :parentCombo"
    	dim args as sql::arguments
    	args.add("parentCombo",curCombo)
    	
    	flag = cn.Execute(sql,args)
    	if flag = 0 then
    		cn.Close()
    		dim msg as c
    		msg = "Could not execute the Sum query. Error reported was: " + cn.CallResult.text
    		msg = js_escape(msg)
    		dim jscmd as c
    		jscmd = "alert('" + msg + "');"
    		sumHols = jscmd
    		exit function
    	end if
    
    	dim rs as sql::resultset
    	rs = cn.ResultSet
    
    	dim holTotal as n
    	holTotal = rs.data(1)
    
    	dim jscmd as c 
    	jscmd = "$('div1').innerHTML = 'Days used by " + curCombo + " - " + holTotal + "';"
    	sumHols = jscmd
    end function
    The error seems to suggest a problem with the data returned. I'll have to leave this until tomorrow for now, but I'll look into arrays and the information passed in the resultset, maybe check the database, and see if I can spot an issue.

  22. #22
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    Here's the best way to find a lot of problems. Put a debug(1) statement right at the top of your function and run in Working Preview mode. Then step through the code, checking for variables. See if things get set, returned, etc. as they should.

  23. #23
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Ah, I didn't know about the debug() function. This will really help!

    Looks like I had some issues with table naming on the database I'm using. Some of them were created with spaces in the names!
    Changing the names would cause all kinds of issues with the grids. Is there some way of encapsulating the table name in the sql statement?
    e.g. SELECT * FROM 'table name' WHERE

    I tried a couple variations with no luck. I'm going to carry on trying to facilitate a name change (since it's now working up to a point), but there are all sorts of problems popping up. Also, rs.data(1) doesn' t seem to be returning a value, but I'll look into that once the database issue is sorted out.

    Thanks for sticking it out!

  24. #24
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    debug(1) will help you step through to the assignment of the recordset to see if there are any records being returned... my guess is not. That's why you're not getting anything our of rs.data(1).
    And try this character around your table name `table name`.

  25. #25
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Sorted out the database naming and determined that ResultSet was returning empty because there were no values in the first record for it to sum
    It's all done and sorted and I've added a couple of extra bits with what I now know.

    Learned quite a bit over the last week.
    Thanks for all the help David; it took a while but we got there in the end! Hopefully this will be a useful thread for anyone looking achieve something similar.
    Last edited by RaEd.; 08-16-2011 at 11:16 AM.

  26. #26
    Member RaEd.'s Avatar
    Real Name
    Paul Atherton
    Join Date
    May 2011
    Location
    St.Helens, England
    Posts
    176

    Default Re: Totalling payments due vs. payments made

    Quote Originally Posted by Davidk View Post
    And try this character around your table name `table name`.
    Using the ` character kicked up some issues for me, it didn't seem to like it at all. In the end I had to rename the table and then went through the grids that used it in the web application and made sure they were using the new name.

  27. #27
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,767

    Default Re: Totalling payments due vs. payments made

    Odd... I tried it here first to make sure it was ok. Still... better with no spaces 'cause they're always trouble.

    I've run into the same problem with the resultset returning empty, for whatever reason. Usually some problem I've caused, but just in case I tried to find some way of testing it and just can't find anything. It seems that many properties of the resultset are not available if it's not set - which kinda makes sense. The ones that are available are of no help in testing.

Similar Threads

  1. applying payments to multiple?
    By Frank Calandro in forum Alpha Five Version 7
    Replies: 8
    Last Post: 01-11-2007, 06:41 PM
  2. Partial payments from here
    By George Corder in forum Alpha Five Version 5
    Replies: 3
    Last Post: 12-29-2002, 12:26 PM
  3. Advice for entering payments
    By Tim Sutherland in forum Alpha Five Version 5
    Replies: 4
    Last Post: 11-19-2002, 09:12 AM
  4. Posting Payments
    By Lloyd Frank in forum Alpha Five Version 4
    Replies: 4
    Last Post: 05-25-2000, 08:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •