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

Report Group by one of four fields

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

    Report Group by one of four fields

    I am trying to write a report to group on one of four fields. I will try to explain as best I can.

    I have a flat table that I need to run a report off of. There are four fields where a person's name could be entered. I need to create the report but have it group on the name of the person no matter what field it is found on.

    An example of what I might have is:
    Field 1 Field 2 Field 3 Field 4
    Name 1 Name 2
    Name 2
    Name 3 Name 1
    Name 3 Name 4 Name 2 Name 1

    What type of formula can I setup for the group to make sure it includes the names for each of the four fields in the details section? I have been out of the loop for a while on writing reports and am just struggling to get the concept figured out without doing it in a painstaking manner.

    Any help or suggestions to get me on the right track are greatly appreciated.

    Thank you,
    Casy

    #2
    Re: Report Group by one of four fields

    Call me crazy, but now might be the time to normalize your data. The use of repeating fields to hold the same data is the source of your present difficulty. If this is something you might consider tell us more about the application and how it came to be like this.

    Comment


      #3
      Re: Report Group by one of four fields

      You should normalize the table, but in case you don't do that, you can do it with an if:
      if(namefield1 <> null_value(), namefield1, if(namefield2<> null_value(),namefiedl2,if(namefield3<>null_value(),namefield3,namefield4)))

      Comment


        #4
        Re: Report Group by one of four fields

        Hi Elliot,
        But that only works if all but one of the fields is blank. I hope he comes back to explain what each of those 4 name fields represents, because that might help in how he might be able to group them. Unless he has repetitive data ( the same name) in more than one field, in which case Tom's suggestion of normalizing data first is the only way to go.
        Robin

        Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

        Comment


          #5
          Re: Report Group by one of four fields

          What I understood is that the name is in one of the 4 fields, if the name is in more than one field, is the same name. If that is the case, the function will still work for that matter.
          i.e. if the name is in field 1 the function will evaluate and will return the name in the first if and the rest will not be evaluated, if the name is in field2 and field 3, it will return the name in the 2nd field and the third and 4th will be skipped. I think he is using the same table in different forms created a distinct name field for each one.
          Last edited by mastermind315; 04-04-2013, 03:42 AM.

          Comment


            #6
            Re: Report Group by one of four fields

            Originally posted by CasyLaib View Post
            An example of what I might have is:
            Field 1 Field 2 Field 3 Field 4
            Name 1 Name 2
            Name 2
            Name 3 Name 1
            Name 3 Name 4 Name 2 Name 1
            Casy
            suggests different names in each field. The title of the post suggests grouping by one of the four.
            This needs clarification.

            Comment


              #7
              Re: Report Group by one of four fields

              Ok, I will see if I can clarify a little bit more. First thank you all for your feedback and help.

              I agree with Tom that the data needs to be normalized and setup differently and when I re-write this as a web based app I will. Currently I am just reporting off of data that I already have so that makes it a little harder.

              The data is people responsible for a task. There can be up to four people assigned to a task because of this the fields will not contain the same data for an individual record. I need to run a report for management that shows who is assigned what task to make sure it is followed up on properly.

              Elliot - I plugged in your formula and it helps but it missing some of the individuals. I am currently working to determine why at this time.

              I hope this helps clarify. Thank you again for your help!

              Comment


                #8
                Re: Report Group by one of four fields

                If you want then to show all the people working then create a calculated field concatenating all the four fields then display the cal field in the report
                Something like this
                crew = concatenate (fieldname1,", ",fieldname2,", ",fieldname3,", ",fieldname4) and in the report use crew
                You can go a little bit more elaborate for when there are no names to not show, but this will be a quick fix and will work.

                Comment


                  #9
                  Re: Report Group by one of four fields

                  Elliot, your approach would show which people are working a given task. But this is available already, right? I think he needs a list grouped by a person, and for each show all the tasks they are working on. this will be very difficult without preprocessing the data to try to normalize it.

                  Comment


                    #10
                    Re: Report Group by one of four fields

                    That was one of my first thoughts and I have set that up but it was not good enough for management. They wanted it listed by each person individually not the group.

                    It appears that this formula: if(namefield1 <> null_value(), namefield1, if(namefield2<> null_value(),namefiedl2,if(namefield3<>null_value(),namefield3,namefield4))) Is only showing the Field1 and Field4 on the report. So it will pull all of the first fields and if there is a user in the fourth field but is skipping over Field2 and Field3.

                    I might just have to work on some scripts to manipulate the data and normailize it but one of my concerns there is that it is going to have to be done every time I run the report until I am given the opportunity to write the application the way it needs to be.

                    Thanks again!

                    Comment


                      #11
                      Re: Report Group by one of four fields

                      What I understood from her clarification he is reporting tasks and the people working in them. She definitely needs to normalize the table, she is aware of that, but in the meantime this will resolve the current issue.
                      Last edited by mastermind315; 04-04-2013, 09:41 AM.

                      Comment


                        #12
                        Re: Report Group by one of four fields

                        Casy that formula will end as soon it finds the first non blank field, try using the concatenate approach that will collect all the names.

                        Comment


                          #13
                          Re: Report Group by one of four fields

                          Casey,

                          If you're wanting a report grouped by person, and for each showing all the tasks they've been assigned you could do the following:

                          1) Create a new table that contains two fields, task and person_name
                          2) Create an append operation that appends task & field1 to the new table, but only if field1 is not blank
                          3) Create an append operation that appends task & field2 to the new table, but only if field2 is not blank
                          4) continue pattern for field3
                          5) continue pattern for field4
                          6) empty the new table
                          7) run the 4 appends
                          8) base your report on the new table

                          Comment


                            #14
                            Re: Report Group by one of four fields

                            Tom I essentially did what you were saying and it seems to have worked properly. This will give me the opportunity to just run the operation and then the report.

                            Thank you both for all of the help. I know I need to get this application written in A5 so I can get everything setup properly but this will get me by and get the reports to management.

                            Thanks again!

                            Comment


                              #15
                              Re: Report Group by one of four fields

                              Casy
                              Just in case it is not apparent to you, if you haven't done this already -
                              That is the practical step to have this continue as a working system without the operations.
                              Create a set with one to many based on task linking the two on which to base the form and report.

                              Comment

                              Working...
                              X