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 of related records - Grid

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

    Count of related records - Grid

    I am looking for help in the best way (fast/efficient) to display a record count in a grid for each row.
    Let me explain

    Currently, I have a table with job information, "Jobs" and a table called "appointments" so what I am wanting to do
    is have the number of upcoming appointments (even if it is zero) show per row as text on a button in a grid using the jobs table as it's main query. The button click would show the upcoming appointment information. So this is based on the >=startdate field in the appointments table.


    I have tried different ways to get the record count, and display per row the number of upcoming events by creating a linked grid and updating it per row - this is way too slow but works. So what I had was a linked grid in the row that essentially showed the number of appointments and the date of those, and even though it was just that info in the linked grid - in a per row update 65-70 records runs very slow to retrieve the info in the linked grid. I then tried using row expanders but really that is about the same thing and isnt exactly what I REALLY want which is to load the grid with the jobs and in (for) each row get a record count of appointments that match the jobid and have a startdate that is >= todays date.

    My next attempt is using a view
    I have a view setup to retrieve only upcoming appointment called "myview" it contains the job and appointment info from curdate() forward.
    I think I could get a record count from the view but again wouldn't this be slow also per row?


    Maybe I need to use a UX for this with a parent-child list and use summary events? Am I asking too much from a grid?
    Maybe a state variable that updates a value per row based on a sql look-up? seems like a slow method as well...
    OR should I modify my sql query in teh first place to display all the jobs with appointments beyond curdate - I did try this but kept getting
    Hope I provided enough info for your opinions! It makes sense in my head, lol.
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

    #2
    Re: Count of related records - Grid

    in mysql you will write a code to select the count of something in a table
    Code:
    SELECT id, appointment_type, COUNT(*)
    FROM appointments
    GROUP BY appointments;
    now that will give you data if make a grid

    Code:
    1    setup cage      5
    9    dispose rabbit 7
    7    collect cage     3
    4   buy coffee         4
    you can hide the id. this will be the data for the grid.
    if the data has what i said above and that many times. then you should be able to write a code in the onClick event of the button or column.

    edit: you can make the count column look like a button if that is what you are after using css.
    Last edited by GGandhi; 09-21-2017, 09:16 PM.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    [email protected]
    Skype:[email protected]
    1 914 924 5171

    Comment


      #3
      Re: Count of related records - Grid

      I am with you so far - I actually am able to get the count using summary values and the dates of the upcoming appointments in a list and using that list in the row as a "linked grid". The problem is that "FOR each row" (which is a unique jobid), makes this a very slow process in loading the rows.
      Dropping that idea due to its slow response time.

      What I have done is to make a view showing ONLY (upcoming) appointments >=curdate and then used that view right outer joined to the jobs table where the start date in the view is null - which will show ONLY those jobs where there is NOT a match with the view by jobid and YES my brain hurts now.

      SO using that sql select statement I created a grid that shows the jobs without upcoming appointments which is called from a button in the jobs list that is then displayed in a tab AND I have done the same for jobs with upcoming appointments using just the view. This works in a timely manner.

      I might try and create a view from the SQL statement I used to show jobs without appointments - if you can even create a view from a join of a table and a view in the hopes that this might run faster in getting the resultset back!

      This seemed like a pretty simple thing to do at first but as I said my brain is struggling right now.

      One thing is for sure the views are faster than joined and filtered multiple table lookups!
      Thanks for the advice GGhandi!
      NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

      Comment


        #4
        Re: Count of related records - Grid

        you might want to post sample data ( or tables structure ) and desired look of the grid to see if it can be done.
        thanks for reading

        gandhi

        version 11 3381 - 4096
        mysql backend
        http://www.alphawebprogramming.blogspot.com
        [email protected]
        Skype:[email protected]
        1 914 924 5171

        Comment


          #5
          Re: Count of related records - Grid

          Charles,

          We do the same thing you are wanting to do. In the pic, the Advise and N-C are counts from another table that has almost 1 million records, yet this is pretty fast.
          counts.png
          What we do is use a view with the following two lines included
          (SELECT COUNT(PROJECT_NUMBER) FROM project_lines WHERE PROJECT_NUMBER = p.PROJECT_NUMBER AND LINE_STATUS = 'Advise') AS ADVISE_COUNT,
          (SELECT COUNT(PROJECT_NUMBER) FROM project_lines WHERE PROJECT_NUMBER = p.PROJECT_NUMBER AND LINE_STATUS = 'Deficient') AS DEFICIENT_COUNT,

          Then just put those two fields in the grid.
          Pat
          Pat Bremkamp
          MindKicks Consulting

          Comment


            #6
            Re: Count of related records - Grid

            Pat - how are you putting the values into the grid? Obviously, a SQL lookup but is it a session or state var? or just return javascript to the control? I am not so sure how to return the value to a control in xbasic using JS - in a UX I use e._set.controlname.value and that works great but no clue in a grid. Also as I said how are you doing that in per row render?
            Glad to hear you're getting it done -successfully getting this done tonight would really be awesome! I hope you will have time tonight to give me a little more insight on your execution. Many, many thanks!

            On another note, my quest in this led me to Doron Farber's posts and videos he has done and that introduced me to row expanders which I do not know WHY I havnt been using those - OMG, if you are using grids they are amazingly simple. In fact, they caused me to rethink my style and now I have a style that I think works even better and looks cleaner. It's been a very productive weekend!
            NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

            Comment


              #7
              Re: Count of related records - Grid

              Charles,

              Maybe you don't know what a view is? I build my views in the MariaDB or MySQL database. You can also do it in the Alpha SQL builder, but I find using Navicat directly in the database to be cleaner.

              You put in fields from a view the same way you put in fields from a table. As far as Alpha knows, advise_count and deficient_count are just two more fields in the table. They show up in the field list and you put them in the grid just like any other field. No JavaScript required.
              Pat Bremkamp
              MindKicks Consulting

              Comment


                #8
                Re: Count of related records - Grid

                Perhaps, I am not fully explaining the situation. As fas as I know as view cannot be used for crud operations like a grid with a detail view without writing xbasic to push back the values to the joined tables of the view.
                I can get the info from the view and know i can use the values of a view. My need or "want" was to show the values in the view for each row in a grid with a detail view for the jobs table. So if my understanding is correct I would use the view to display the count, but if I wanted to update the two joined tables I would still then have to write xbasic to update the two joined tables - correct? I did read that a view can be used to update the related tables but I am not sure AS would do that by simply using the view. Am I right in that part of my understanding?
                NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                Comment


                  #9
                  Re: Count of related records - Grid

                  So far the rows in the grid - each one pulling the count from the view has been a slow user experience - which is why i was asking how I might approach it differently.
                  NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                  Comment


                    #10
                    Re: Count of related records - Grid

                    Looking back at your post,I am not sure how to add an additional field to the query and perhaps that is where I am not "getting it" in your suggestion. Normally I would just pick a table and add the fields - would i create a custom sql query to start with? Is that how? I can loook into that if that is the case.
                    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                    Comment


                      #11
                      Re: Count of related records - Grid

                      why not post your table structure and what you would like to see in the grid, i am sure then it will be easy for people to help you.
                      thanks for reading

                      gandhi

                      version 11 3381 - 4096
                      mysql backend
                      http://www.alphawebprogramming.blogspot.com
                      [email protected]
                      Skype:[email protected]
                      1 914 924 5171

                      Comment


                        #12
                        Re: Count of related records - Grid

                        Originally posted by CharlesParker View Post
                        Looking back at your post,I am not sure how to add an additional field to the query and perhaps that is where I am not "getting it" in your suggestion. Normally I would just pick a table and add the fields - would i create a custom sql query to start with? Is that how? I can loook into that if that is the case.
                        Yes Charles,

                        You build your sql based on the table + the view(s) and you base the grid on the sql.
                        Something like
                        select t1.f1, t1.f2, t1,f3, v1.fcount
                        from t1
                        join v1 on t1.f4 = v1.f1 and t1.f5 = v1.f2

                        where t1 represents your table, v1 represents your view
                        f1, f2, f3 are fields from table 1
                        fcount is the field that counts from your view
                        and the join is the most important one preferably bound on ONE unique key that is both in the table and view.

                        hth

                        Pieter

                        Comment


                          #13
                          Re: Count of related records - Grid

                          OK - THAT makes sense to me. Thanks! Might take me a minute to get it sorted in my head, but now I see. Like I said, I have already built a view and believe it or not I have already built a component based on the join using the view and the jobs table, I should have seen the implications and the count() in that query because it is pretty much what you are describing albeit in reverse. I joined the view and the table to get the number of jobs WITHOUT an appointment by filtering it. Now, I just need to reconfigure the filter and I will have EXACTLY what I want, thank you for pointing out what should have been so very obvious to me...
                          NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                          Comment


                            #14
                            Re: Count of related records - Grid

                            OK - WOW is all I can say! I finally figured out how to do it. It took me two views and a custom SQL query with the final view to then be able to put a dynamic image in the grid based on the number of upcoming appointments! So if there are 3 new appointments scheduled into the future(includes "today") then you will see an Icon with a number 3 in the row and there is no longer a speed issue since this is all based on what AA considers just another field value as Pat and pieter so kindly pointed out to me.
                            Since I have almost NO experience with views this DID take me some time to figure out what the correct syntax was for MySQL workbench. Alpha Software REALLY helped me out with the fact that you can convert your query in the builder to about any database structured query - even so, I still had to tweak it BUT it was a huge help.


                            So my first view was a join on my jobs table with my tasks (might be better referred to as appointments) where the idea was to get a view with ONLY tasks that were from today's date and beyond.
                            Then I built a second view based on the jobs table and the previous view to get a count of each "job"
                            SO now I had the COUNT that I was looking for - there is undoubtedly a more efficient way to do this, however, I am a novice.

                            Anyway, with the required count in the new view - I was able to create my grid with my original table and add the second view as a read-only left outer joined table - grab the count field in the grid using a dynamic image (client-side) and I now GOT IT.

                            I could NEVER have done this with your suggestions - thank you!
                            NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                            Comment


                              #15
                              Re: Count of related records - Grid

                              Originally posted by CharlesParker View Post
                              I could NEVER have done this with your suggestions - thank you!
                              Thanks Charles for making me laugh when I was reading this sentence more carefully :) :)

                              Comment

                              Working...
                              X