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

count records on linked table filter by a data field in the current row

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

    count records on linked table filter by a data field in the current row

    Hi there I'm a newbie and this is my first post.

    This is not what I'm actually working on but the following example illustrates what I need to achieve...

    I have two tables, an orders table and an invoice table

    Orders_tbl
    order_ID
    orderNumber
    count

    invoice_tbl
    invoice_ID
    order_ID
    code
    qty

    What I want to do is populate the count field in Order_tbl with the count of related records in the invoice_tbl, filtered by the order_ID. If this was an SQL statement it woudl look something like...

    count(invoice_tbl.invoice_ID) where order_ID = Orders_tbl.order_ID

    ...thats probably wrong too as I'm no SQL expert either but hopefully you get the idea :)

    appreciate any help, suggestions or advice

    cheers
    Brad

    #2
    Re: count records on linked table filter by a data field in the current row

    ...underwhelming response :)

    Comment


      #3
      Re: count records on linked table filter by a data field in the current row

      Maybe a more specific question would get more response. When do you want to update count? When 'posting' an invoice or maybe once a week by pressing a button on a menu. The devil is in the detail.

      Comment


        #4
        Re: count records on linked table filter by a data field in the current row

        I would make the Orders table a view and do a select count(*) into the invoice table. Here is a sample of what I do...
        Select
        FullName,
        ServiceCnt = (Select COUNT(*) from ServiceDex where ServiceDex.Patients_ID = Patients.Patients_ID)
        from Patients

        Result is:

        Smith 3
        Jones 4

        Comment


          #5
          Re: count records on linked table filter by a data field in the current row

          To Ivasic: THANKYOU, I was reading some of your posts last night and you are clearly a guru, I was really impressed with your vids...Ivasic I am new into this and I need a mentor, I can afford say USD60.00 per hour, that is probably not enough but I'm happy to pay in 15 minutes blocks with a USD15.00 minimium. Would you be willing to help me for this rate? I don't know how much time I will need probably anywhere between 1-5 hours per week is my best guess, I have just started building my first big app and I have a number of other ones I'm keen to get started on but I'm finding I get stuck, I cant afford too much time to study (beyond a reasonable threshold) and I clearly don't know how to post questions to get quality responses quickly as Gary has pointed out (I appreciated your response) so I need help. I am happy to pay up front say in 5 hour blocks then as you spend time helping me you deduct time and let me know when I've got less than hour left and I'll top it up again. What do you think?. My expectation would be that I'd be able to get reasonable answers within 24 hours most days (is that reaosnable) If you're ok with this I would need a bank account number, a swift number and an IBAN number as well as your banks address.

          Point in case with your response at least I have somewhere to start looking now, I got to the point I had no idea how to approach the problem, I didn't know if I needed a function, an expression, a setting somewhere, I wasn't sure if i needed to do it in the table the grid a javascript server side or client side event or something else? I didnt know if I needed to make a set or it would be somehow connected to the row expander function???

          My app is using native alpha 5 tables so I don't think I can create a view. On this basis I'll rebuild the back end in sqlserver and build a view and try as you suggest. I'm now going to study SQL and try and get this to work...Thanks again for responding and please let me know if you are able to mentor me.

          To Gary: thanks for taking the time to respond, the child grid is displayed within a row expander, I would have expected the count field to refresh when the parent grid was loaded and when the child grid was updated, I guessed it would have been a calculated field somehow but have no idea if you need to program this or it is built in function managed by the linked ability of grids within alpha 5. I did not think I would need to build a button to event drive the result I wanted... my example was probably poor, I'll get better

          Comment


            #6
            Re: count records on linked table filter by a data field in the current row

            What I want to do is populate the count field in Order_tbl with the count of related records in the invoice_tbl, filtered by the order_ID.
            wouldn't that value be:
            Code:
            tablecount("invoice_tbl","order_id="+quote(order_id))
            with a character id value and with a numeric
            Code:
            tablecount("invoice_tbl","order_id="+order_id)
            If you're ok with this I would need a bank account number, a swift number and an IBAN number as well as your banks address.
            Sounds like those offers I get from South Africa where grandmother died and I am now privileged to her huge family fortune and all I need to do is give them my personal information!
            Mike W
            __________________________
            "I rebel in at least small things to express to the world that I have not completely surrendered"

            Comment


              #7
              Re: count records on linked table filter by a data field in the current row

              Take a look at this little video. I'm using the Northwind database in SQL. I added a couple of views to make it work.
              http://www.screencast.com/t/UlciEnldUn

              Comment


                #8
                Re: count records on linked table filter by a data field in the current row

                Originally posted by Mike Wilson View Post
                wouldn't that value be:
                Code:
                tablecount("invoice_tbl","order_id="+quote(order_id))
                with a character id value and with a numeric
                Code:
                tablecount("invoice_tbl","order_id="+order_id)


                Sounds like those offers I get from South Africa where grandmother died and I am now privileged to her huge family fortune and all I need to do is give them my personal information!

                Comment


                  #9
                  Re: count records on linked table filter by a data field in the current row

                  Hi Mike, thanks for this I'll try it out, I did try a tablecount() earlier but I must have been doing it wrong or putting it in the wrong place...appreciate the reply...I agree it sounds like a scam :) but I'm very genuine I promise, just in a hurry, I've been stuck on this issue for nearly a month now and as a noob I've been hitting a lot of brick walls so I'm happy to pay

                  Comment


                    #10
                    Re: count records on linked table filter by a data field in the current row

                    absolutely incredible - you are the master, I'm going to try this, will let you know how I go

                    THANKYOU!!!

                    Comment


                      #11
                      Re: count records on linked table filter by a data field in the current row

                      Hi Lee, mate this is amazing, so what I've done is ditch the dbf db, download SQL express plus the tools and learnt how to build a SQL DB, set up a test one and tried your idea and it worked! I cant believe it, it was so easy as you explained it in SQL - thankyou thankyou thankyou I'm now styding sql querries flat out!

                      ...my next issue is that I dont have a detail view like your example, I have a seperate child table (linked through a row expander) so when I enter a new record in the child table it does not update the parent 'count' (my test field to prove he concept) record until I reload the parent table. I'm guessing the answer is I have to build a refresh function to the parent grid in the AfterInsertRecord server side event (on a similiar function) of the child grid...so I need to figure that out next...dont suppose you could assit (I have not written a funciton yet ) :) (read sheepish grin)

                      THANKS anyway, you've been amazing with your assistance.

                      Comment


                        #12
                        Re: count records on linked table filter by a data field in the current row

                        Check out video 64. There is a parent grid refresh method.
                        //Get a pointer to the parent Grid object that opened this Grid and execute a .refresh() method on the parent.
                        var po = {grid.object}.getParentObject();
                        //If this Grid does not have a parent then 'po' will be false.
                        if(po) po.refresh()
                        Try putting this method in your child grid save or when QTY is changed inline java.

                        Comment


                          #13
                          Re: count records on linked table filter by a data field in the current row

                          Lee thanks again...I've not had a chance to review this but will do so over the weekend, thanks for the nudge in the right direction you're a star!

                          Comment

                          Working...
                          X