Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Totalling payments due vs. payments made

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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
    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!

    Comment


      #3
      Re: Totalling payments due vs. payments made

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

      Comment


        #4
        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.

        Comment


          #5
          Re: Totalling payments due vs. payments made

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

          Comment


            #6
            Re: Totalling payments due vs. payments made

            Excellent sql views.

            Comment


              #7
              Re: Totalling payments due vs. payments made

              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/

              Comment


                #8
                Re: Totalling payments due vs. payments made

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

                Comment


                  #9
                  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!

                  Comment


                    #10
                    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

                    Comment


                      #11
                      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!!

                      Comment


                        #12
                        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.

                        Comment


                          #13
                          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

                          Comment


                            #14
                            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, 06:39 AM.

                            Comment


                              #15
                              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.

                              Comment

                              Working...
                              X