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

Multiple Variable Querying Multiple Fields

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

    Multiple Variable Querying Multiple Fields

    I have 3 variables (input via XDialog) they are: TG1, TG2, TG3
    I'm trying to develop a query that will return all records in which the three variable match any of five title group fields.

    I've come up with:
    (Titleg1 = Var->TG1 ) .or. (Titleg2 = Var->TG1 ) .or. (Titleg3 = Var->TG1 ) .or. (Titleg4 = Var->TG1 ) .or. (Titleg5 = Var->TG1 ) .or. (Titleg1 = Var->TG2 ) .or. (Titleg2 = Var->TG2 ) .or. (Titleg3 = Var->TG2 ) .or. (Titleg4 = Var->TG2 ) .or. (Titleg5 = Var->TG2 ) .or. (Titleg1 = Var->TG3 ) .or. (Titleg2 = Var->TG3 ) .or. (Titleg3 = Var->TG3 ) .or. (Titleg4 = Var->TG3 ) .or. (Titleg5 = Var->TG3 )


    but this doesn't work

    Does anyone know a solution or what I'm doing wrong?

    PS
    This function works to query the three variable against one title group field
    ((Var->TG1=Titleg1).or.(Var->TG2=Titleg1).or.(Var->TG3=Titleg1))

    #2
    Re: Multiple Variable Querying Multiple Fields

    Originally posted by rdecle01
    I have 3 variables (input via XDialog) they are: TG1, TG2, TG3
    I'm trying to develop a query that will return all records in which the three variable match any of five title group fields.

    I've come up with:
    (Titleg1 = Var->TG1 ) .or. (Titleg2 = Var->TG1 ) .or. (Titleg3 = Var->TG1 ) .or. (Titleg4 = Var->TG1 ) .or. (Titleg5 = Var->TG1 ) .or. (Titleg1 = Var->TG2 ) .or. (Titleg2 = Var->TG2 ) .or. (Titleg3 = Var->TG2 ) .or. (Titleg4 = Var->TG2 ) .or. (Titleg5 = Var->TG2 ) .or. (Titleg1 = Var->TG3 ) .or. (Titleg2 = Var->TG3 ) .or. (Titleg3 = Var->TG3 ) .or. (Titleg4 = Var->TG3 ) .or. (Titleg5 = Var->TG3 )


    but this doesn't work

    Does anyone know a solution or what I'm doing wrong?

    PS
    This function works to query the three variable against one title group field
    ((Var->TG1=Titleg1).or.(Var->TG2=Titleg1).or.(Var->TG3=Titleg1))
    "Doesn't work" doesn't tell much. You probably need to group your query variable tests with parentheses. I would imagine you want something like...

    Code:
    ((Titleg1 = Var->TG1  ) .or. (Titleg2 = Var->TG1  ) .or. (Titleg3 = Var->TG1  ) .or. (Titleg4 = Var->TG1  ) .or. (Titleg5 = Var->TG1  )) .and. ( (Titleg1 = Var->TG2 ) .or. (Titleg2 = Var->TG2  ) .or. (Titleg3 = Var->TG2  ) .or. (Titleg4 = Var->TG2  ) .or. (Titleg5 = Var->TG2  )) .and. ((Titleg1 = Var->TG3  ) .or. (Titleg2 = Var->TG3  ) .or. (Titleg3 = Var->TG3  ) .or. (Titleg4 = Var->TG3  ) .or. (Titleg5 = Var->TG3  ))
    if you wanted records where all five fields held one of the variables.

    It also might be easier to express the tests as

    inlist(alltrim(titleg1),var-TG1,var-TG2,var-TG3,var-TG4,var-TG5)
    There can be only one.

    Comment


      #3
      Re: Multiple Variable Querying Multiple Fields

      Rob, tell us more about the query. Am I right in assuming all 3 of the search keys must be present in the 5 fields? i.e. You don't want a record "found" unless all 3 keys are present (one per field) in any of the 5 possible fields?

      If so I'd recommend you study up on the query by form operators, and then use query by form to generate the filter expression. You're going to need something like this (pseudo code):

      TG1 is in F1 or F2 or F3 or F4 or F5
      AND
      TG2 is in F1 or F2 or F3 or F4 or F5
      AND
      TG3 is in F1 or F2 or F3 or F4 or F5

      Where F1, F2, F3, F4 and F5 are your 5 fieldnames

      -- tom

      Comment


        #4
        Re: Multiple Variable Querying Multiple Fields

        Right now I have two seperate querys that work.

        The table that I'm querying has 7 fields:
        Title, Title Description, TitleGroup1, TitleGroup2, TitleGroup3, TitleGroup4, TitleGroup5

        I have an X dialog box that inputs 6 variable (3 for Title, 3 for Title Group):
        Titles: Var T1, T2, T3
        Title Groups: Var TG1, TG2, TG3

        I have a query written to return all the records in which the title values match the T variable:
        inlist(alltrim(Title1),Var->T1,Var->T2,Var->T3)
        For Example:
        T1 is set to AAA
        T2 is set to PRM

        Result:
        AAA ADMINISTRATIVE ASSISTANT 011
        PRM PRODUCTION MANAGER 005 011 07A 07B 07C

        I have a query written to return all the records in which the title group variables match any of the the T variable:
        (inlist((Titleg1),Var->TG1,Var->TG2,Var->TG3)).OR.(inlist((Titleg2),Var->TG1,Var->TG2,Var->TG3)).OR.(inlist((Titleg3),Var->TG1,Var->TG2,Var->TG3)).OR.(inlist((Titleg4),Var->TG1,Var->TG2,Var->TG3)).OR.(inlist((Titleg5),Var->TG1,Var->TG2,Var->TG3))

        This also works.

        What I can't get to work is a combined query. To query for only the records that match Query1 and Query2. I've tried combining the two with an and statement and parentheses

        Comment


          #5
          Re: Multiple Variable Querying Multiple Fields

          I think it's time for you to post a sample of the table, and sample search key values we can plug into the variables. Tell us which records meet the desired search criteria so we can check our work as we try to develop a specific solution for you.

          -- tom

          Comment


            #6
            Re: Multiple Variable Querying Multiple Fields

            Tom,
            I need to be able to run a query where I can select multiple titles and/or multiple title groups and have the result reflect the records where values for both variable groups are contained.

            I also need the querys to return all records if no variable is entered in either or both of the variable groups.

            For instance:
            Query TitleSearch AND Query TitleGroup Search

            Any assistance is much appreciated

            Comment


              #7
              Re: Multiple Variable Querying Multiple Fields

              Rob, ok. Now, using the sample that you gave us, give us a specific example and list the record numbers of each record that meets your search criteria. i.e. supply several titles and several title groups, and tell us which records actually meet your search criteria. -- tom

              Comment


                #8
                Re: Multiple Variable Querying Multiple Fields

                Example:

                If I enter AAA MMM and PRM as Title variables: T1, T2, and T3 and leave the
                Title group Variables blank I need to get:

                AAA,ADMINISTRATIVE ASSISTANT 011
                MMM MKTG ADMINISTRATOR 07C 7C2
                PRM PRODUCTION MANAGER 005

                If I leave the Title variables blank and Title group Variables: TG1 = 011 and TG2 = 005 I need to get:
                All 90 or so records where fields TitleG1 through 5 have either a 011 or 005


                If I enter AAA MMM and PRM as Title variables: T1, T2, and T3 and
                Title group Variables: TG1 = 07C I need to get:
                MMM MKTG ADMINISTRATOR 07C 7C2

                If I enter AAA MMM and PRM as Title variables: T1, T2, and T3 and
                Title group Variables: TG1 = 07C TG2= 005 I need to get:
                MMM MKTG ADMINISTRATOR 07C 7C2
                PRM PRODUCTION MANAGER 005

                Thanks for the help.

                Comment


                  #9
                  Re: Multiple Variable Querying Multiple Fields

                  I think this will do it, and can easily be extended to cover groups TG3, TG4 and TG5.

                  Code:
                  'Created By  : Tom
                  'Updated By  : Tom
                  
                  dim t1 as C = "AAA" 
                  dim t2 as C = "MMM"
                  dim t3 as C = "PRM"
                  
                  dim tg1 as C = "07C"
                  dim tg2 as C = "005"
                  
                  tbl = table.open("title table")
                  
                  query.order = ""
                  query.options = "N"
                  
                  filter1 = "inlist(title1,t1, t2, t3)"  [COLOR="Red"]'filter for the titles[/COLOR]
                  
                  [COLOR="Red"]'now do the filter for the groups[/COLOR]
                  filter2 = "inlist2(var->tg1, titleg1+','+titleg2+','+titleg3+','+titleg4+','+titleg5).or. inlist2(var->tg2,titleg1+','+titleg2+','+titleg3+','+titleg4+','+titleg5)"
                  [COLOR="Red"]
                  'now AND them[/COLOR]
                  filter = "("+ filter1 + ") .and. ("+ filter2 +")"
                  query.filter = filter
                  ix = tbl.query_create()
                  tbl.close()

                  Comment


                    #10
                    Re: Multiple Variable Querying Multiple Fields

                    Thanks Tom, works great.
                    Last edited by rdecle01; 09-11-2006, 10:36 AM.

                    Comment


                      #11
                      Re: Multiple Variable Querying Multiple Fields

                      Show us your script so we check what you did. (don't retype, use copy / paste) -- tom

                      Comment


                        #12
                        Re: Multiple Variable Querying Multiple Fields

                        This is a very basic question (as I'm still learning XBasic Code):
                        How do I set the variable to pickup on values that my XDialog box inputs?
                        Rather than the values coded into this script:

                        dim t1 as C = "AAA"
                        dim t2 as C = "MMM"
                        dim t3 as C = "PRM"

                        dim tg1 as C = "07C"
                        dim tg2 as C = "005"

                        Comment


                          #13
                          Re: Multiple Variable Querying Multiple Fields

                          Here's crude example.

                          The sequence is simple. If the script sequence that runs the query is part of the same script that displays the xdialog and prompts for the variable values then nothing else need be done. You don't need to re-declare the variables later on in the same script. See the script called "QryRun3".

                          In script "QryRun2" I declared the vars to have "shared" scope, and re-declared them with the same scope later on. This runs fine also.

                          -- tom

                          Comment


                            #14
                            Re: Multiple Variable Querying Multiple Fields

                            Tom,
                            Here's where I'm at, I have a form called "title table query form" it has six listboxes to input my variables. I have a button under these boxes that I have tried to setup to run the query script you helped me out with; onPush. I'm not quite sure where I've gone wrong. I think I have declared the variables correctly.
                            -Rob

                            Comment


                              #15
                              Re: Multiple Variable Querying Multiple Fields

                              In Filter2 you need to use INLIST2() three times... you only do it twice. Try changing that to see if things work correctly. Otherwise, if you still need help then supply me with a list of search keys and results...

                              -- tom

                              Comment

                              Working...
                              X