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

Boolean search for records?

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

    Boolean search for records?

    I'm constructing a photo database. Each photo has one or more people in it. I need to be able to find all photos containing Person1 and Person2 (both must be in each photo)

    The kludge solution is to keep all the names in a memo field in each photo record, and query the memo fields with a boolean expression. Is there a more elegant way? For example, I have a table of people, each with a Name_ID, and a table of photo-person combinations. If a photo has 3 people in it, this table has three records. I'd like to filter that table, leaving only the records that have the names I'm seeking with a common photo_ID.

    This is an application. It would use the solution for several other kinds of parent tables: photos, videos, speeches, events, articles etc.

    TIA
    GRB

    #2
    Re: Boolean search for records?

    Sounds like you need to make two tables in a one-to-many relationship, in one of two ways:

    1. Under a single person's name you can enter Photo_IDs of all the photos he appears in. This means a parent table of persons (with a Name_ID key) and a child table of photos. Alternatively,

    2. Under each single photo, you enter the Name_IDs of all the persons that appear in that photo. This means the table of photos will be the parent and the table of persons will be the child.
    Jim

    Comment


      #3
      Re: Boolean search for records?

      Originally posted by jmatienza View Post
      Sounds like you need to make two tables in a one-to-many relationship, in one of two ways:

      1. Under a single person's name you can enter Photo_IDs of all the photos he appears in. This means a parent table of persons (with a Name_ID key) and a child table of photos. Alternatively,

      2. Under each single photo, you enter the Name_IDs of all the persons that appear in that photo. This means the table of photos will be the parent and the table of persons will be the child.
      Thanks for the prompt reply, Jim. Both valid suggestions, but I still don't see how to find joint appearance in a photo. In case 1, it seems that each record applies to only one person, and each photo ID must be a separate field. That opens the question of how many photo ID fields to establish -- they should be open-ended. Then, how do I find the juncture of that person with another person in N common photos? Keep in mind, I'm not looking for photos of you or me, but photos of you and me.

      Perhaps Case 2 is a solution: I could put all the person IDs in a single character field, then query on a $-inclusion for Person 1, then within the result set, query for inclusion of person 2, etc. That's better than the idea I had of using a memo field, with actual names. Is that what you had in mind, or is there a better way?
      GRB

      Comment


        #4
        Re: Boolean search for records?

        What you describe is indeed a bit tricky to do an ordinary query to get the desired results
        In the Learn alpha.com website, Bill Hannisberg nicely illustrates an example of creating an easy to use search to find articles with several topics in common.His example is somewhat similar to your original thought about keeping the childid's in a text field on the parent.
        You could alternatively use a summary operation to accomplish this, but the problem with that is it creates new tables, and can create a problem in a multi-user environment.

        Comment


          #5
          Re: Boolean search for records?

          Splendid, John. It's reassuring to know I was headed in the right direction, and the article provided assurance that this is as elegant as it's going to get.

          For my next trick: Retaining the position of each person in the photo (row and "column"). But I can handle that with a link to a child table.

          Thanx.
          GRB

          Comment


            #6
            Re: Boolean search for records?

            George,

            I think you already have the ingredients to do what you want which is basically a many to many relationship. I would make a set with the photo table as parent and the photo-person table as child linked one to many then to that link the person table one to one.

            Open the default form for the set. Start the query genie and make sure 'Query records at which level' is set to 'Crosslevel'. This should be the default. The first item on the 'Condition1' page is a dropdown combobox with the tables in your set, choose the person table. Then choose the name field in the left most list box and 'is equal to' as the operator. Next choose or enter a value to look for. Run the query. This will filter the photo table (parent) for records that have a child record with the name you are looking for. You now have the photos incude person1.

            Run the query again with the name of person2. A menu will pop up asking to search all records (basically starting over) or searching within the current selection. Choose the latter. This will narrow down your list of photos as you want. The browse displaying the child records will still show all the child records for the photo so if you search for photos that contain person1 and person2 this only filters the parent and you will stiil be able to see who else if any is in the photo as well.

            I only used the query genie as an example. There are Action Scripting and xbasic coomands that will accomplish the same thing. It depends a bit on how you want to choose the persons you are looking for.
            Tim Kiebert
            Eagle Creek Citrus
            A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

            Comment


              #7
              Re: Boolean search for records?

              George,

              OK, I'll chime in, also.

              You will have many photos. You will need a table for the photos with a unique identifier for each photo. Make it a character field. You will have many people. You will need a table for each person with a unique identifier for each person. Make it character field.

              Your request is "look for a photo that contains Bob and Judy". SO, the Parent is the photo (1) and the many is the Child. So, you have to attach the photoID to people.

              Make a set 1:M with Parent= Photo and Child = people. Link them with the photoID. For each photo that a person is in, they will now have that photoID attached to them.

              Then to search for a photo with BOB Smith and BILL Jones, use a List-List dialog box to select the person and get that person's unique id number. Upfront make script for 1 person search. 2 person search. Three person search until you get more sophisticated, or figure a way to use a multiselect (don't like these with big lists). However you do it, you need to get select the persons and get their UniqueID into a variable.

              Then use 2 functions:
              1. table.external_record_content_get() to filter the table to person's uniqueID and generate a list of the photos that person is in .
              2. word_in_common() between the lists to get a subtraction and a match between the two lists.

              An example of a three people search (Bob, Bill, Amy) list subtraction is as follows. The red is to generate the list in the Interactive Window to see how it operates. The Green is the code needed after all the tables and the variables are acquired to run the matching. The purple is required in both.

              ' this list would be generated with using table.external_record_content_get() and Bob's unique id number - ListA = table.external_record_content_get("People","Peopleid= "+s_quote(var->BOB'sID),"PeopleID","")

              dim listA as C
              listA= <<%a
              00000005
              00000007
              00000011
              00000013
              00000015
              %a%


              ' this list would be generated with using table.external_record_content_get() and Bill's unique id number - listB = table.external_record_content_get("People","Peopleid= "+s_quote(Var->Bill'sID),"PeopleID","")

              dim listB as C
              ListB = <<%a%
              =00000003
              00000005
              00000006
              00000007
              00000009
              00000010
              00000011
              00000013
              00000015
              %a%


              ' this list would be generated with using and Amy's unique id number - listC = table.external_record_content_get("People","Peopleid= "+s_quote(Var->Amy'sID),"PeopleID","")

              dim listC as C
              listC = <<%a%
              00000005
              00000001
              00000011
              00000042
              %a%


              ' this creates a list of the matches between List A and ListB
              dim vLAinLB as C
              vLAinLB = word_in_common(listA, listB, crlf())

              ' this matches the matches between A&B with C
              dim listfinal as C
              listfinal = word_in_common(vLAinLB, listC, crlf())

              ? listfinal
              = 00000005
              00000011

              PhotosID's 00000005 and 00000011 contain all three people, and the photo's can be located.

              That's my thought.
              Mike W
              __________________________
              "I rebel in at least small things to express to the world that I have not completely surrendered"

              Comment


                #8
                Re: Boolean search for records?

                Great, Tim! That's the kind of sleek solution I wanted. Yes, I have to do it all through scripting, as this is a client application. If it gets too sticky, I can fall back to parsing a string of IDs. But this provides the opportunity to carry along positional information on each photo-person record.

                Most of my A5 work (22 years!) has been for my own benefit. I haven't needed to use queries much, so I was unaware of the power they carry.

                I'll work it through manually, and take a look at the script recorder for inspiration.

                Thanx.
                GRB

                Comment


                  #9
                  Re: Boolean search for records?

                  George,
                  I found your question intriguing, so I played with a structure that might effect what I believe you were seeking. I resolved to the concept that people will be registered to a media unit (picture, video, essay, etc.) and therefore I incorporated a registration table, right or wrong. Probably not what you had in mind, but fun to work through, learned some new stuff.

                  Fun application. Do you know about ACDSee?

                  www.acdsee.com
                  Mike W
                  __________________________
                  "I rebel in at least small things to express to the world that I have not completely surrendered"

                  Comment


                    #10
                    Re: Boolean search for records?

                    OK, I posted the wrong one.
                    Mike W
                    __________________________
                    "I rebel in at least small things to express to the world that I have not completely surrendered"

                    Comment


                      #11
                      Re: Boolean search for records?

                      Haven't opened my presents yet, but I'm sure it'll be interesting. Thanks.

                      I considered ACDsee, along with Adobe Album. Both would work just fine for the photos, but this needs to be an integrated archival package. I only mentioned the photos because whatever works there will work with everything else. There will be tabs for photos, videos, speeches, newspaper articles, committees, etc.

                      The idea is to retrieve every record relating to a set of input parameters: people, events, dates. Should be pretty powerful. I also plan to integrate the retrieval with Office by placing the record information on the Clipboard. For example, if we're going to email a photo to the media, this will transfer the who-where-when-why data directly to the body of the message. The client's secretarial staff needs all the help they can get.

                      Thanks again.
                      GRB
                      GRB

                      Comment

                      Working...
                      X