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

Index_create.....

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

    Index_create.....

    Doing a join operation to create a temp table. Of course, I guess, this gets rid of any indexes you create for the table when it overwrites it. So am needing to create the index in xbasic. Could someone point out my error in the following code?
    Code:
    dim tbl as p
    dim tbl2 as p
    tbl = table.open("tmp_available")
    tbl2 = table.open("tmp_usage")
    tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date","U")
    indx = tbl2.index_create_end("tmp_usage") [COLOR="Red"]<< get "Use Index_create_begin first" error in debug[/COLOR]
    tbl2.index_primary_put("Use_Date")
    tbl.batch_begin()
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    	rec = tbl2.fetch_find(tbl.Room_id+""+tbl.Use_date)
    	if rec > 0 then
    Thanks.
    Ernie

    #2
    Re: Index_create.....

    Hi Ernie,
    In the Index builder the string returns as fieldname+" "+fieldname so I am wondering if you have too many quotes when passing this as an expression to the function. Have you tried "fieldname+" "+fieldname" in the tbl2.index_create_begin() statement? Also if your script is erring it is leaving the table open; you might want to toss in a goto label to get the table closed - just in case.
    Robin

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

    Comment


      #3
      Re: Index_create.....

      Robin,
      Thanks for the response. Here's what I'm getting now with this setup.
      Code:
      dim tbl as p
      dim tbl2 as p
      dim indx as p
      tbl = table.open("tmp_available")
      tbl2 = table.open("tmp_usage",FILE_RW_EXCLUSIVE)
      tbl2.index_create_begin("Use_Date",s_quote(tmp_usage->Room_Id)+""+dtoc(tmp_usage->use_date),"U")
      indx = tbl2.index_create_end()  [COLOR="Red"] << get the first errror here[/COLOR]
      tbl2.index_primary_put("Use_Date")
      hen
      Tried yours and get the second attached error.
      Code:
      tbl2.index_create_begin("Use_Date","Room_Id+""+Use_Date","U")
      indx = tbl2.index_create_end()
      Haven't been able to find anything in the help other than the basic methods explained, which isn't enough detail for my limited knowledge.
      Here's what I'm doing. Trying to build a table of availabilities.
      1. I do a join between two tables to get the tmp_usage table.
      2. Run a script that reads thru a rooms table and adds every room for each day requested from a date range passed in. Results table is tmp_available.
      3. Now I'm trying to read thru this tmp_available table and with each record see if there is a matching record, based on the room_id + use_date fields. If so, update a couple of fields in the tmp_available table.

      I may just try my own join so I can add the info to the tmp_usage table without wiping out the index. Then I won't have to worry about this problem. But would be nice to find out what I'm doing wrong here.
      Ernie
      Appreciate your help.
      Ernie

      Comment


        #4
        Re: Index_create.....

        [quote=enstorms;457853]Tried yours and get the second attached error.
        Code:
        tbl2.index_create_begin("Use_Date","Room_Id+""+Use_Date","U")
        indx = tbl2.index_create_end()
        [/qoute]

        Code:
        <TBL>.INDEX_CREATE_BEGIN( Tag_Name as C,   Index_Expr as C [, Filter_Expr as C   [, Index_Type as C ]]   )
        The function has 4 arguments. You can't skip argument 3 - filter.
        That's why you got the logical error. A filter must evaluate to a logical.
        Use:
        Code:
         tbl2.index_create_begin("Use_Date","Room_Id+Use_Date","","U")
        Also your second argument isn't correct. Try what I've listed, but I don't have your table to try it with your field definitions. So use the index builder for the table to make the index and copy the order from it and paste that into the order expression of the function.
        Last edited by Al Buchholz; 05-11-2008, 01:29 PM.
        Al Buchholz
        Bookwood Systems, LTD
        Weekly QReportBuilder Webinars Thursday 1 pm CST

        Occam's Razor - KISS
        Normalize till it hurts - De-normalize till it works.
        Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
        When we triage a problem it is much easier to read sample systems than to read a mind.
        "Make it as simple as possible, but not simpler."
        Albert Einstein

        http://www.iadn.com/images/media/iadn_member.png

        Comment


          #5
          Re: Index_create.....

          Code:
          tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date","U")
          The method .index_create_begin() allows you to have four parameters with the third and fourth being optional. If you choose to use the fourth (index type) you must tell the method that you are using it and skipping the third (filter).

          Code:
          tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date",[B][SIZE="5"][COLOR="Red"]"",[/COLOR][/SIZE][/B]"U")

          Must have been typing at the same time as Al.
          There can be only one.

          Comment


            #6
            Re: Index_create.....

            Thanks, guys. Appreciate the help. That makes sense. Was wondering about skipping the 3rd argument without putting anything in there. Still trying to get a handle on how these expressions work with quotes.
            Ernie

            Comment


              #7
              Re: Index_create.....

              Originally posted by enstorms View Post
              Still trying to get a handle on how these expressions work with quotes.
              Ernie
              We could help you with this one but we would need to know what you are really trying to accomplish. Is this just an index on two fields? Why is there a + "" + between them which appears to do nothing?

              Figuring out how to set up quotes is something that seems to bother a lot of people but I found it quite logical once I broke it down into separate "pieces". Here's something I actually wrote for someone else a year or two ago. Maybe it will help:

              Building complex filter expressions in xbasic that involve quotes is one of the toughest things to get your head around but you'll be able to build really complex expressions with no problem once you do. The first "method" will be primarily for explanatory purposes - so you can start to understand the logic - then I'll show you how I do it in actual practice.

              Start by building an example expression of a specific filter. I suggest you do this in the expression builder initially. The expression builder helps you get the initial filter right. Start with something simple like: (Note that my field names are always 10 characters or less and end with "f".)
              Veh_Manf="Ford" .and. Veh_Modelf="Taurus"

              Copy this filter to the appropriate place in your xbasic script.

              Now we need to put quotes around the whole thing because the basic construction is Query.filter = "filter string" HOWEVER, the problem is that there are already quotes inside the string so first we'll 'escape' those quotes with backslashes:
              Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"

              then add the quotes around the outside:
              "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"

              We now have a filter string that we can pass to the query.filter variable but it won't be very generic until we replace the actual values with variables. That means we'll have to break this character string into multiple strings with variables between each string section. Let's start by just breaking it into string sections where the actual values being searched for (Ford and Taurus) are separate strings. Remember that every string (or string section in this case) must be enclosed in quotes to identify it as a string. If it wasn't enclosed in quotes, the program would look for a field by that name. The result is:
              "Veh_Manf=\"" + "Ford" + "\" .and. Veh_Modelf=\"" + "Taurus" + "\""

              Note that we have basically just added " + " at various locations to break it up into multiple strings. Maybe it will help to show that we could actually do this but it wouldn't make much sense:
              "Veh_M" + "anf=\"" + "F" + "o" + "r" + "d" + "\" .and. " + "Veh_Modelf=\"" + "Tau" + "rus" + "\""
              (Yes, it appears messy and you may have to look at that a bit but it makes the point that this is just a series of string values at this point.)

              Now we simply replace the values being searched for with the appropriate variable name:
              "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc + "\""

              The prefix "var->" is optional in this case because the script has no confusion. It's not enclosed in the quotes, it isn't a function call, and isn't a control command like IF, WHILE, =, >, etc., and it's in a script so it must be a variable. Using "var->" is perfectly acceptable but not necessary. As far as I'm concerned, using var-> in a script is just extra typing:
              "Veh_Manf=\"" + var->Man_chc + "\" .and. Veh_Modelf=\"" + var->Model_chc + "\""

              Note that, in this case, we are not passing the variable name to the layout
              - we are only using the variable to build the filter string. By the time the string is sent to the layout, the string will have the actual field values in it - in my example that will be "Ford" and "Taurus". If a variable is passed to the layout, it must be a GLOBAL variable. (SHARED also works in some cases but GLOBAL always works.)

              Beware when using variables in filters. A filter expression passed to a layout operates under a different assumption than an expression in a script. A script which contains an "unidentified name" assumes that name is a variable. If a filter expression containing an "unidentified name" is passed to a layout, the layout assumes the unidentified name must be a field name. This is a common error - a global variable is used in a filter expression but the developer forgets to add "var->" in front of it to identify it as a variable and the layout reports a "field not found" error.

              Personally, I almost always use the above method for building strings with variables. But that's probably because that's the way I was forced to learn it - there was no other choice at the time. It's also the most universal method - you can't count on every programming language having something like an S_Quote() function. Since the toughest part is to come up with the correct quotes and people seem to have a hard time figuring out where to use the backslashes, Alpha came up with the S_Quote() function.

              Now that I've gone through all that, the way I actually do it now that I understand the requirements is from left to right. (Unless it is a really complex expression.) I start with the first part of the string and look for where the first variable will be - the first part of the string ends where the first variable starts. If the first variable will be a character value, then the string has to include a quote for the variable, if it's a date value it has to include the {, and nothing is required if it's a numeric value:
              "Veh_Manf=\""
              "Veh_blddtf={"
              "Veh_widthf="

              Now add the variable. REMEMBER THAT WE ARE CREATING A STRING. If it's a date variable, convert it to a character string. If it's a numeric value, convert that to a character string also. Even a logical value will need to be converted to a string. Remember that the final result must be one long string:
              "Veh_Manf=\"" + man_chc
              "Veh_blddtf={" + dtoc(date_chc)
              "Veh_widthf=" + str(width_input,10,2)

              Now continue with the next part of the string up to the next place you want to use a variable. Remember to start with the closing 'operator' ( \" or } ) as appropriate:
              "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\""

              Now add the next variable:
              "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc

              And, finally, the last string which in this case is nothing but an embedded closing quote which is just an escaped quote within quotes "\"":
              "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc+ "\""

              When you are certain that there will be no single quotes and no apostrophes in the string values, another option is to use single quotes inside the double quotes. I find this method even easier and it's easy to read in the code editor. (Unfortunately, it's not easy to read in most word processors so I'll do the best I can.)
              "Veh_Manf='" + Man_chc + "' .and. Veh_Modelf='" + Model_chc + "'"

              Assuming Man_chc = "Ford" and Model_chc = "Taurus", the result passed to the report or other layout is the expression we originally started with:
              Veh_Manf="Ford" .and. Veh_Modelf="Taurus"
              or, using single quotes:
              Veh_Manf='Ford' .and. Veh_Modelf='Taurus'
              You can now see why it's important to make sure there are no commas or other single quotes in the filter string if you chose so use single quotes when building your filter expression.

              Comment


                #8
                Re: Index_create.....

                Cal,
                Which only goes to show how hard this really is to grasp! I always thought that enclosing the variables as "+var+" was some syntax thing that parsed the variables themselves. And when used in a filter, necessitated adding the single quotes to wrap it. I think I see the difference in what I am supposed to be accomplishing.

                I think therefore I am (not an idiot...?) :D
                Robin

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

                Comment


                  #9
                  Re: Index_create.....

                  Originally posted by MoGrace View Post
                  Cal,
                  Which only goes to show how hard this really is to grasp! I always thought that enclosing the variables as "+var+" was some syntax thing that parsed the variables themselves. And when used in a filter, necessitated adding the single quotes to wrap it. I think I see the difference in what I am supposed to be accomplishing.

                  I think therefore I am (not an idiot...?) :D
                  Hard to grasp initially - yes. But once you realize that it's a series of strings with an occasional variable thrown in, then beat your way through 2-3 'conversions', it quickly becomes much easier. At least, it did for me. Then after 10 or so it starts becoming truly easy. However, if you stop after 1 because that one was difficult, you will never learn.

                  Comment


                    #10
                    Re: Index_create.....

                    Robin

                    You have a great memory. I think you may have been thinking of replace_parameters().

                    That's a good option for building a filter into a character string, and then using the string as an argument in a function
                    Al Buchholz
                    Bookwood Systems, LTD
                    Weekly QReportBuilder Webinars Thursday 1 pm CST

                    Occam's Razor - KISS
                    Normalize till it hurts - De-normalize till it works.
                    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                    When we triage a problem it is much easier to read sample systems than to read a mind.
                    "Make it as simple as possible, but not simpler."
                    Albert Einstein

                    http://www.iadn.com/images/media/iadn_member.png

                    Comment


                      #11
                      Re: Index_create.....

                      Originally posted by Al Buchholz View Post
                      Robin

                      You have a great memory. I think you may have been thinking of replace_parameters().

                      That's a good option for building a filter into a character string, and then using the string as an argument in a function
                      Thanks for the help link. Look at the 2 examples given:

                      Example (Local variables)
                      Run a report where the filter is based on parameters.
                      dim whatLastname as C
                      dim whatDate as D
                      whatLastname = "Smith"
                      whatDate = {12/18/1967}
                      Filter = "lastname = [varC->whatLastname] .and. " + \"date_of_birth > = [varD->whatDate]"
                      filter = replace_parameters(filter, local_variables() )
                      report.print("customer",filter)

                      Note : If the "whatLastname" and "whatDate" variables are DIMmed as SHARED variables, then this example can be considerably simplified because you can refer the variables directly in the filter expression, and you therefore do not need to use the REPLACE_PARAMETERS() function:

                      Use shared variables.
                      dim SHARED whatLastname as C
                      dim SHARED whatDate as D
                      whatLastname = "Smith"
                      whatDate = {12/18/1967}
                      filter = "lastname = var->whatLastname .and. date_of_birth = var->whatDate"
                      report.print("customer", filter)
                      Now when the genie creates these codes, how is one to know the difference in the syntax being used? Note especially the way the quotes are used to create the string in the first example and not used in the 2nd - the part I put in red. Since I have never used replace_parameters outside of a genie created script, I would never have found this info! Lucky for me I use mostly shared or global variables when writing filter expressions (like the 2nd example). Usually because they never work otherwise!

                      Also in the 1st example, why does the 2nd " not need to be escaped with the \ too? I would have thought it should be written like this when .and. is used - including parentheses:

                      Filter = "(lastname = [varC->whatLastname])+\" .and. \"+(date_of_birth > = [varD->whatDate])"

                      or perhaps like this using single quotes:

                      Filter = "(lastname = [varC->whatLastname])+' .and. '+(date_of_birth > = [varD->whatDate])"
                      Last edited by MoGrace; 05-13-2008, 11:56 AM.
                      Robin

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

                      Comment


                        #12
                        Re: Index_create.....

                        Thanks so much for all the information. Cal, I have printed out your post and will study it and, I'm sure, refer to it often. Just one question so far.
                        then add the quotes around the outside:
                        "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"
                        Should there be an extra " at the end?
                        then add the quotes around the outside:
                        "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\""
                        Ernie

                        Comment


                          #13
                          Re: Index_create.....

                          Originally posted by enstorms View Post
                          Should there be an extra " at the end?
                          Yes, nice catch - thanks! Don't know how I missed that one.

                          Comment


                            #14
                            Re: Index_create.....

                            However, if you stop after 1 because that one was difficult, you will never learn.
                            Words to live by!
                            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

                            Working...
                            X