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
Devcon 2020 Goes Virtual! LEARN MORE >

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

Another Fetching Question

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    Another Fetching Question

    Hello,

    I am having a problem with this code:

    Code:
     
veh_tbl = table.open("vehinfo")
veh_tbl.fetch_first()
first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
WHILE .not. veh_tbl.fetch_eof()
 fil.write_line(" <D06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
 fil.write_line("      <D06_01>"+alltrim(veh_tbl.Unitvehicilenumber)+"</D06_01>")
 WHILE veh_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. veh_tbl.fetch_eof()
 fil.write_line("      <D06_03>"+alltrim(veh_tbl.D06_03)+"</D06_03>")
 veh_tbl.fetch_next()
 end while
 fil.write_line(" <D06_04_0>")
 first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
 End While
veh_tbl.close()
vep_tbl = table.open("vehpersonnel")
vep_tbl.fetch_first()
first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
fil.write_line("                  <D06_04>"+alltrim(vep_tbl.D06_04)+"</D06_04>")
fil.write_line("                  <D06_05>"+alltrim(vep_tbl.Numberofpersonnel)+"</D06_05>")
WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
vep_tbl.fetch_next()
end while
    fil.write_line(" </D06_04_0>")
first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
vep_tbl.close()

    Here is what i am trying to achieve:
    Code:
     
<D06 Status="A" Date="2009-07-09">
  <D06_01>Vehicle 1</D06_01>
  <D06_03>7370</D06_03>
  <D06_04_0>
   <D06_04>9990</D06_04>
   <D06_05>2</D06_05>
  </D06_04_0>
  <D06_04_0>
   <D06_04>9991</D06_04>
   <D06_05>2</D06_05>
  </D06_04_0>
  <D06_06>2</D06_06>
  <D06_07>2006</D06_07>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
 </D06>
 <D06 Status="A" Date="2009-07-09">
  <D06_01>Vehicle 2</D06_01>
  <D06_03>7370</D06_03>
  <D06_04_0>
   <D06_04>5432</D06_04>
   <D06_05>2</D06_05>
  </D06_04_0>
  <D06_04_0>
   <D06_04>9876</D06_04>
   <D06_05>2</D06_05>
  </D06_04_0>
  <D06_06>2</D06_06>
  <D06_07>2006</D06_07>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
 </D06>


    The values in D06_04 and D06_05 are related to the Value in D06_01

    the values in D08_01 thru D08_10 are also related to the value in D06_01


    Here is the set structure:

    Vehinfo

    |
    |____________Vehpersonnel
    |____________VehMileage

    I have uploaded the database for examination..

    thanks for all the help.. Without the excellent help i could never accomplish what i have done so far...
    Tags: None
    #2
    Re: Another Fetching Question

    It looks like the section you don't have processed in your code is between the red segments.

    Code:
    [COLOR="Red"]</D06_04_0>[/COLOR]
  <D06_06>2</D06_06>
  <D06_07>2006</D06_07>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
  <D06_08_0>
   <D06_08>2006</D06_08>
   <D06_09>-5</D06_09>
   <D06_10>-5</D06_10>
  </D06_08_0>
[COLOR="red"] </D06>[/COLOR]
    As such it appears that you need to put the code to generate this


    Code:
    WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
	vep_tbl.fetch_next()
END WHILE
fil.write_line(" </D06_04_0>")
[COLOR="Red"]------------------------------
'new section goes here
------------------------------[/COLOR]
first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
vep_tbl.close()
    I don't see anthing that generates the final </D06> but I assume this is a work in progress.
    There can be only one.

    Comment

      #3
      Re: Another Fetching Question

      Thanks for the reply Stan,

      i am still working on the next section

      when i run the code i get this:

      Code:
       
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
 <D06_04_0>
 <D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST2</D06_01>
      <D06_03>2</D06_03>
 <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      this is what i need...

      <D06 Status="A" Date="2009-07-09">
      <D06_01>Test 1</D06_01>
      <D06_03>1</D06_03>
      <D06_04_0>
      <D06_04>1</D06_04>
      <D06_05>2</D06_05>
      </D06_04_0>
      <D06_04_0>
      <D06_04>2</D06_04>
      <D06_05>3</D06_05>
      </D06_04_0>

      if you look at the set the values in the table are:

      these values are for test only..

      Unitvehiclenumber: Test1
      values for D06_03 : 1
      values for D06_04: 1 and 2
      values for D06_05: 2 and 3

      as you can see by the example:
      Values D06_04 and D06_05 are related to D06_01

      i dont know if you can follow this.

      Comment

        #4
        Re: Another Fetching Question

        Thanks for the reply Stan,

        i am still working on the next section

        when i run the code i get this:

        Code:
         
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
 <D06_04_0>
 <D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST2</D06_01>
      <D06_03>2</D06_03>
 <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
        this is what i need...

        Code:
         
<D06 Status="A" Date="2009-07-09">
<D06_01>Test 1</D06_01>
<D06_03>1</D06_03>
<D06_04_0>
              <D06_04>1</D06_04>
              <D06_05>2</D06_05>
</D06_04_0>
<D06_04_0>
               <D06_04>2</D06_04>
               <D06_05>3</D06_05>
</D06_04_0>
        if you look at the set the values for test 1 in the table are:

        these values are for test only..

        Unitvehiclenumber: Test1
        values for D06_03 : 1
        values for D06_04: 1 and 2
        values for D06_05: 2 and 3

        as you can see by the example:
        Values D06_04 and D06_05 are related to D06_01

        i dont know if you can follow this.

        Comment

          #5
          Re: Another Fetching Question

          I think it is going to be something like

          Code:
          veh_tbl = table.open("vehinfo")
veh_tbl.fetch_first()
first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
WHILE .not. veh_tbl.fetch_eof()
	fil.write_line(" <D06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
	fil.write_line("      <D06_01>"+alltrim(veh_tbl.Unitvehicilenumber)+"</D06_01>")
	WHILE veh_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. veh_tbl.fetch_eof()
		fil.write_line("      <D06_03>"+alltrim(veh_tbl.D06_03)+"</D06_03>")
		fil.write_line(" <D06_04_0>")'moved this line up here
			vep_tbl = table.open("vehpersonnel")
			vep_tbl.fetch_first()
			first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
			fil.write_line("                  <D06_04>"+alltrim(vep_tbl.D06_04)+"</D06_04>")
			fil.write_line("                  <D06_05>"+alltrim(vep_tbl.Numberofpersonnel)+"</D06_05>")
			WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
				vep_tbl.fetch_next()
			END WHILE
			fil.write_line(" </D06_04_0>")
			first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
			vep_tbl.close()
		veh_tbl.fetch_next()
	END WHILE
	
	first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
END WHILE
veh_tbl.close()
          There can be only one.

          Comment

            #6
            Re: Another Fetching Question

            Thanks stan,

            Close

            here is what the code does..

            Code:
             
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
 <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      <D06_03>2</D06_03>
 <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
            D06_03 does not repeat itself..

            only the D06_04 and D06_05

            with the test 1 example

            it should be:

            Code:
             
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
 <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      <D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>

            Comment

              #7
              Re: Another Fetching Question

              I modifed the code a bit this is the new code:

              Code:
               
veh_tbl = table.open("vehinfo")
veh_tbl.fetch_first()
first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
WHILE .not. veh_tbl.fetch_eof()
 WHILE veh_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. veh_tbl.fetch_eof()
 fil.write_line(" <D06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
 fil.write_line("      <D06_01>"+alltrim(veh_tbl.Unitvehicilenumber)+"</D06_01>")
 fil.write_line("      <D06_03>"+alltrim(veh_tbl.D06_03)+"</D06_03>")
 fil.write_line("      <D06_04_0>")'moved this line up here
   vep_tbl = table.open("vehpersonnel")
   vep_tbl.fetch_first()
   first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
   WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
   fil.write_line("                  <D06_04>"+alltrim(vep_tbl.D06_04)+"</D06_04>")
   fil.write_line("                  <D06_05>"+alltrim(vep_tbl.Numberofpersonnel)+"</D06_05>")
  fil.write_line(" </D06_04_0>")
    vep_tbl.fetch_next()
    end while
    
   first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
   vep_tbl.close()
  veh_tbl.fetch_next()
 END WHILE
 first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
END WHILE
veh_tbl.close()
              This gives this result..
              Code:
               
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
      <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>
 <D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST2</D06_01>
      <D06_03>2</D06_03>
      <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>

              It should be:

              <D06 Status ="A" Date="2009-07-09">
              <D06_01>TEST1</D06_01>
              <D06_03>1</D06_03>
              <D06_04_0>
              <D06_04>1</D06_04>
              <D06_05>2</D06_05>
              </D06_04_0>
              <D06_04>2</D06_04>
              <D06_05>3</D06_05>
              </D06_04_0>
              <D06 Status ="A" Date="2009-07-09">
              <D06_01>TEST2</D06_01>
              <D06_03>2</D06_03>
              <D06_04_0>
              <D06_04>3</D06_04>
              <D06_05>4</D06_05>
              </D06_04_0>
              <D06_04>4</D06_04>
              <D06_05>5</D06_05>
              </D06_04_0>

              everything else is ok except for the d06_04 and d06_05 codes.. I think i also have to fix the <D06_04_0> Tag...

              Comment

                #8
                Re: Another Fetching Question

                I tried in index:

                i also fixed the tags:

                still having problem filtering the D06_04 and D06_05

                heres the code:

                Code:
                 
veh_tbl = table.open("vehinfo")
veh_tbl.fetch_first()
first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
WHILE .not. veh_tbl.fetch_eof()
 WHILE veh_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. veh_tbl.fetch_eof()
 fil.write_line(" <D06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
 fil.write_line("      <D06_01>"+alltrim(veh_tbl.Unitvehicilenumber)+"</D06_01>")
 fil.write_line("      <D06_03>"+alltrim(veh_tbl.D06_03)+"</D06_03>")
 
   vep_tbl = table.open("vehpersonnel")
   vep_tbl.index_primary_put("Unitvehicilenumber")
   vep_tbl.fetch_first()
   first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
   WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
   fil.write_line("      <D06_04_0>")'moved this line up here 
   fil.write_line("                  <D06_04>"+alltrim(vep_tbl.D06_04)+"</D06_04>")
   fil.write_line("                  <D06_05>"+alltrim(vep_tbl.Numberofpersonnel)+"</D06_05>")
      fil.write_line(" </D06_04_0>")
    vep_tbl.fetch_next()
    end while
    
   first_Unitvehicilenumber = vep_tbl.Unitvehicilenumber
   vep_tbl.close()
  veh_tbl.fetch_next()
 END WHILE
 first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
END WHILE

                heres what i get when i run the code:

                Code:
                 
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
      <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      <D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>
 <D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST2</D06_01>
      <D06_03>2</D06_03>
      <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      <D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>
                this is almost correct:

                but i need this:

                Code:
                 
<D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST1</D06_01>
      <D06_03>1</D06_03>
      <D06_04_0>
                  <D06_04>1</D06_04>
                  <D06_05>2</D06_05>
 </D06_04_0>
      <D06_04_0>
                  <D06_04>2</D06_04>
                  <D06_05>3</D06_05>
 </D06_04_0>
 <D06 Status ="A" Date="2009-07-09">
      <D06_01>TEST2</D06_01>
      <D06_03>2</D06_03>
      <D06_04_0>
                  <D06_04>3</D06_04>
                  <D06_05>4</D06_05>
 </D06_04_0>
      <D06_04_0>
                  <D06_04>4</D06_04>
                  <D06_05>5</D06_05>
 </D06_04_0>
                i cant seem to filter the d06_04 and d06_05 in this code. they seem to be repeating themselves

                Comment

                  #9
                  Re: Another Fetching Question

                  Yes, the code opens the vehpersonnel table each time through the outer while loop and processes all records. We will have to find a way to filter that table for the appropriate linking value in the vehinfo table.

                  More tomorrow.
                  There can be only one.

                  Comment

                    #10
                    Re: Another Fetching Question

                    I think this works.

                    Code:
                    veh_tbl = table.open("vehinfo")
veh_tbl.fetch_first()
first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
WHILE .not. veh_tbl.fetch_eof()
	fil.write_line(" <D06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
	fil.write_line("      <D06_01>"+alltrim(veh_tbl.Unitvehicilenumber)+"</D06_01>")
	WHILE veh_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. veh_tbl.fetch_eof()
		fil.write_line("      <D06_03>"+alltrim(veh_tbl.D06_03)+"</D06_03>")
		vep_tbl = table.open("vehpersonnel")
		query.description = "Temporary Query"
		query.order = "unitvehicilenumber"
		query.options = "I"
		query.filter = "unitvehicilenumber = " +quote(first_Unitvehicilenumber)
		ix = vep_tbl.query_create()
		vep_tbl.fetch_first()
		WHILE vep_tbl.Unitvehicilenumber = first_Unitvehicilenumber .and. .not. vep_tbl.fetch_eof()
			fil.write_line(" <D06_04_0>")
			fil.write_line("                  <D06_04>"+alltrim(vep_tbl.D06_04)+"</D06_04>")
			fil.write_line("                  <D06_05>"+alltrim(vep_tbl.Numberofpersonnel)+"</D06_05>")
			vep_tbl.fetch_next()
			fil.write_line(" </D06_04_0>")
		END WHILE
		ix.drop()
		vep_tbl.close()
		veh_tbl.fetch_next()
	END WHILE
	
	first_Unitvehicilenumber = veh_tbl.Unitvehicilenumber
END WHILE
veh_tbl.close()
                    There can be only one.

                    Comment

                      #11
                      Re: Another Fetching Question

                      Yes it does... Now when i add the next section should i use the same format...

                      Comment

                        #12
                        Re: Another Fetching Question

                        The same general structure at least.

                        In the beginning you stated

                        The values in D06_04 and D06_05 are related to the Value in D06_01
                        Since we aren't working with a set, and I'm not sure it would be better if we were, and we are opening the two tables separately we must explicitly find and process the records in the "inner" table loop which are matches to the "outer" table loop current record.
                        There can be only one.

                        Comment

                          #13
                          Re: Another Fetching Question

                          Thanks for the outstanding help.. I repeated the same structure and i think i have it, at least for now.. Now on to the next part.. I will try and figure it out from what i have learned so far. Thank you again...

                          Comment

                          Previous template Next
                          Working...
                          X