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

Problem with queries

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

    Problem with queries

    Although I can use queries quite happily, I seem to get to a "Tide-mark" on how many values I can query in any one query.

    I can have anything from one value to find, to sometimes over 1000 values to find, My Table "Logging" has 516,000 records.
    I can use query filters like (reg="G-XWBB".or.reg="HB-JCT") for a smallish number of search values, but beyond a certain point the filter string is truncated by A5 and the query fails. I then tried reading the values into a table and access the results in a Set (the entered values as master and "Logging" as child), again this worked of a fashion, but becomes very slow and cumbersome as the total number of records grows.

    Is there a way of build filters that will cater for any amount of "Reg=".....".or." ?

    thanks
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

    #2
    Re: Problem with queries

    The query string truncation is a nuisance, but I suspect it is necessary within Xbasic.
    Can you explain a bit more about the volume please?
    If you're just looking for Reg numbers - and say you want a list of 1000, then you may need to sub-caregorise the reg numbers into groups, or use something different like fuel type, airfield, seats etc.
    If you really need 1000 then you could separate into blocks and upload to a transaction table for processing.

    What do you intend to do with the list of 1000 records?
    See our Hybrid Option here;
    https://hybridapps.example-software.com/


    Apologies to anyone I haven't managed to upset yet.
    You are held in a queue and I will get to you soon.

    Comment


      #3
      Re: Problem with queries

      Hi Graham.

      How are you generating the list of 1000 records (do you have that many columns you are checking) ?
      Gregg
      https://paiza.io is a great site to test and share sql code

      Comment


        #4
        Re: Problem with queries

        In your post yesterday you showed us an image with a radio button that had 21 selections. Are these referring to indexed fields in the table? If they are, a drop down list might work better as you can set up the variable with display|value so what the user selects returns the field to sort by and then another text box (or two if a range is needed in a conditional object) can be displayed for the user to enter the search criteria. That should help the query you build be more concise based on just those variables using a select statement to build the filter for the query.
        Last edited by MoGrace; 10-04-2019, 11:05 AM.
        Robin

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

        Comment


          #5
          Re: Problem with queries

          in this particular instance I am logging when seen, where seen and seen as against a particular airframe.
          Im trying to make it so I have the least amount of typing to do.
          the input file is a straight text file.

          Code:
          delete MyCollection
          dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
          dim shared vLogText     as c = ""
          dim shared vFilter 	as c = ""
          vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
          {Background=#204+153+255}
          {Font=Trebuchet ms,8}
          {region}
          [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
          {endregion};
          {line=1,0};
          {region}
          <OK!OK> <Cancel!CANCEL>
          {endregion};
          %dlg%,<<%code%
          if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
          	a_dlg_button = ""
          end if
          %code%)
          if vResult = "OK" then
          	fp = file.open(vLogFileName,FILE_RW_SHARED)
          	WHILE .not. fp.eof()
          		text = fp.read_line()
          		vCountry = ""
          		IF left(text,1)="#" THEN
          			Seendate  = substr(text,2,10)
          			LocateNo  = substr(text,13,2)
          			SeenWhere = substr(text,16)
          		else
          			if ("," $ text) then
          				vCountry = ut(substr(rtrim(text),at(",",text)+1))
          				text = left(text,at(",",text)-1)
          			end if
          			if ("/" $ text) then
          				vReg = word(text,1,"/",1)
          				vCode = word(text,2,"/")
          			else
          				vReg = text
          				vCode = ""
          			end if
          			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
          		END IF
          	END WHILE
          	fp.close()
          	dim global MyCollection as U
          	rec_count = *count(vLogText)
          	dim MyFilter[rec_count] as p
          	MyFilter.initialize_properties("REG|CODE|COUNTRY|SEENDATE|LOCATENO|SEENWHERE",vLogText)
          	myCollection.initialize("1|2|3|4|5|6","1","1|2|3|4|5|6",vLogText)
          	for x = 1 to rec_count
          		vFilter = vFilter + "reg='"-rtrim(MyFilter[x].reg-"'.or.")
          	next x	
          	vFilter = left(vFilter,len(vFilter)-4)
          	vOrder = "REG"
          	DIM Shared varP_logging as P
          	DIM layout_name as c 
          	layout_name = "logging_Detail_log"
          	DIM tempP as p
          	tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
          	if is_object(tempP) then
          		if tempP.class() = "form" .or. tempP.class() = "browse" then
          			tempP.activate()
          		else
          			varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
          		end if
          	else
          		varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
          	end if
          end if
          the input data format is:
          #12/08/1986|01|South Cerney
          06 blue,RUSSIANFEDER
          07 black,RUSSIANFEDER
          08 red,RUSSIANFEDER
          200/XC,FRANCE
          WZ755/BGA3481,UK
          #04/10/2019|01|{Paganhill}
          C-GBKF
          D-EEFV
          D-EFJN
          D-EHDW
          D-EHLE
          using a global collection so I can pass the relevant when,where,as to the correct record in the Viewd Browse

          this has the following code on the onRowdblclick for the loaded form

          Code:
          dim global MyCollection as U
          t = table.current()
          mylog = MyCollection_get(rtrim(t.reg))
          if t.as = "" then
          	t.change_begin()
          	t.H      = word(myLog,4,"|")
                  t,code = word(myLog,2,"|")
          	t.Where  = word(MyLog,5,"|")
          	t.When   = word(myLog,3,"|")
          	t.as     = rtrim(t.Reg)
          	t.change_end(.t.)
          else
          	reply = ui_msg_box("","Confirm Removal of Logging Data for "+this.reg,UI_QUESTION_SYMBOL+UI_YES_NO)
          	if reply = UI_YES_SELECTED then
          		t.change_begin()
          		t.H = ""
          		t.where = ""
          		t.when = ""
          		t.as = ""
          		t.change_end(.t.)
          	end if
          end if
          topparent.refresh_layout()
          --
          Support your local Search and Rescue Unit, Get Lost!

          www.westrowops.co.uk

          Comment


            #6
            Re: Problem with queries

            I think I get it -maybe.
            Have you thought about using a cascading Tree ( Robin is good at these ), so you can click through the items, each of which will show a drop down list.
            You should be able to get all the info if it is available, through mouse clicks only.
            See our Hybrid Option here;
            https://hybridapps.example-software.com/


            Apologies to anyone I haven't managed to upset yet.
            You are held in a queue and I will get to you soon.

            Comment


              #7
              Re: Problem with queries

              Actually if using a collection, Tim Kiebert might be the one to ask.
              Robin

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

              Comment


                #8
                Re: Problem with queries

                I calculated that I could have a maximum of 64 iterations in my query.filter so my next attempt is thus:

                Code:
                delete MyCollection
                dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
                dim shared vLogText     as c = ""
                dim shared vFilter 		as c = ""
                vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
                {Background=#204+153+255}
                {Font=Trebuchet ms,8}
                {region}
                [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
                {endregion};
                {line=1,0};
                {region}
                <OK!OK> <Cancel!CANCEL>
                {endregion};
                %dlg%,<<%code%
                if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
                	a_dlg_button = ""
                end if
                %code%)
                if vResult = "OK" then
                	fp = file.open(vLogFileName,FILE_RW_SHARED)
                	WHILE .not. fp.eof()
                		text = fp.read_line()
                		vCountry = ""
                		IF left(text,1)="#" THEN
                			Seendate  = substr(text,2,10)
                			LocateNo  = substr(text,13,2)
                			SeenWhere = substr(text,16)
                		else
                			if ("," $ text) then
                				vCountry = ut(substr(rtrim(text),at(",",text)+1))
                				text = left(text,at(",",text)-1)
                			end if
                			if ("/" $ text) then
                				vReg = word(text,1,"/",1)
                				vCode = word(text,2,"/")
                			else
                				vReg = text
                				vCode = ""
                			end if
                			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
                		END IF
                	END WHILE
                	fp.close()
                	dim global MyCollection as U
                	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
                	dim entry_count as n = *count(vLogText)
                	dim vLogData[entry_count] as p
                	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
                	dim vFilterTxt[0] as c
                	dim index as n
                	for block = 0 to int(entry_count/64 + 1)
                		vFilter = ""
                		for iteration = 1 to 64
                			index = block * 64 + iteration
                			if index > entry_count then
                				exit for
                			end if
                			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
                		next iteration
                		vFilterTxt[]=left(vFilter,len(vFilter)-4)
                	next Block
                	vOrder = "REG"
                	for iterate = 1 to Block
                		DIM Shared varP_logging as P
                		layout_name = "logging_Detail_log"
                		DIM tempP as p
                		tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
                		if is_object(tempP) then
                			if tempP.class() = "form" .or. tempP.class() = "browse" then
                				tempP.activate()
                			else
                				varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
                			end if
                		else
                			varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
                		end if
                		if is_object(varP_logging) then
                			varP_Logging.close()
                		end if
                	next iterate
                end if
                end
                when I try and iterate the different filter statements/form it only allows me to display the first filter/form then ends.
                what do I need to do to make the script loop through the filtered forms?
                Last edited by Graham Wickens; 10-06-2019, 04:06 AM.
                --
                Support your local Search and Rescue Unit, Get Lost!

                www.westrowops.co.uk

                Comment


                  #9
                  Re: Problem with queries

                  I have been teaching myself how to use arrays and collections, so tried this problem using arrays.

                  Code:
                  delete MyCollection
                  dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
                  dim shared vLogText     as c = ""
                  dim shared vFilter 		as c = ""
                  vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
                  {Background=#204+153+255}
                  {Font=Trebuchet ms,8}
                  {region}
                  [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
                  {endregion};
                  {line=1,0};
                  {region}
                  <OK!OK> <Cancel!CANCEL>
                  {endregion};
                  %dlg%,<<%code%
                  if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
                  	a_dlg_button = ""
                  end if
                  %code%)
                  if vResult = "OK" then
                  	fp = file.open(vLogFileName,FILE_RW_SHARED)
                  	WHILE .not. fp.eof()
                  		text = fp.read_line()
                  		vCountry = ""
                  		IF left(text,1)="#" THEN
                  			Seendate  = substr(text,2,10)
                  			LocateNo  = substr(text,13,2)
                  			SeenWhere = substr(text,16)
                  		else
                  			if ("," $ text) then
                  				vCountry = ut(substr(rtrim(text),at(",",text)+1))
                  				text = left(text,at(",",text)-1)
                  			end if
                  			if ("/" $ text) then
                  				vReg = word(text,1,"/",1)
                  				vCode = word(text,2,"/")
                  			else
                  				vReg = text
                  				vCode = ""
                  			end if
                  			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
                  		END IF
                  	END WHILE
                  	fp.close()
                  	dim global MyCollection as U
                  	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
                  	dim entry_count as n = *count(vLogText)
                  	dim vLogData[entry_count] as p
                  	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
                  	dim vFilterTxt[0] as c
                  	dim index as n
                  	' Build filter Text
                  	for block = 0 to int(entry_count/64 + 1)
                  		vFilter = ""
                  		for iteration = 1 to 64
                  			index = block * 64 + iteration
                  			if index > entry_count then
                  				exit for
                  			end if
                  			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
                  		next iteration
                  		vFilterTxt[]=left(vFilter,len(vFilter)-4)
                  	next Block
                  	block = vFilterTxt.first_empty()-1
                  	vFilterTxt.resize(block)
                  	vOrder = "REG"
                  	dim idx as n
                  	[COLOR="#FF0000"]' build Display Data
                  	dim vDisplayData[9999] as p
                  	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
                  	for iterate = 2 to block
                  		vDisplayData.initialize_from_table("Logging",vFilterTxt[iterate],vOrder,.t.)
                  	next iterate
                  	rec_numb = vDisplayData.first_empty()
                  	vDisplayData.resize(rec_numb-1)[/COLOR]
                  	title_text = "Enter Logging"
                  	ui_dlg_box("@="+title_text,<<%dlg%
                  {Background=#153+204+255}
                  {region1}
                  {font=Courier New,7}
                  {endregion2};
                  {region=3}
                  [%O==FmtCurrLogg(eval(value))%.60,16idx^#vDisplayData!select];
                  {endregion=3}
                  {region}
                  {line=2,0};
                  {justify=center}
                  <OK><CLOSE>
                  {endregion};
                  %dlg%,<<%code%
                  	if a_dlg_button = "select" then
                  		t = table.open("logging")
                  		query.filter = "unique="-quote(rtrim(vDisplayData[idx].unique))
                  		showvar(query.filter)
                  		query.order = "recno()"
                  		t.query_create()
                  		ThisReg = myCollection.get(rtrim(vDisplayData[idx].reg))
                  		if rtrim(t.as) = "" then
                  			t.change_begin()
                  			t.H      = word(ThisReg,5,"|")
                  			t.Where  = word(ThisReg,6,"|")
                  			t.When   = word(ThisReg,4"|")
                  			t.as     = t.reg
                  			t.change_end(.t.)
                  		else
                  			reply = ui_msg_box("","Confirm Removal of Logging Data for "+word(ThisReg,1,"|"),UI_QUESTION_SYMBOL+UI_YES_NO)
                  			if reply = UI_YES_SELECTED then
                  				t.change_begin()
                  				t.h = ""
                  				t.where = ""
                  				t.when = ""
                  				t.as = ""
                  				t.change_end(.t.)
                  			end if
                  		end if
                  		a_dlg_button = ""
                  	end if
                  %code%)	
                  end if
                  end
                  FUNCTION FmtCurrLogg as c (ar as p)
                  	FmtCurrLogg = padr(rtrim(ar.reg),12," ")+padr(rtrim(ar.Code),10," ")+padr(rtrim(ar.H),3," ")+ar.When+" "+ar.Where
                  end function
                  just wondering if there is a quicker method of appending all the queried data into one array?
                  --
                  Support your local Search and Rescue Unit, Get Lost!

                  www.westrowops.co.uk

                  Comment


                    #10
                    Re: Problem with queries

                    found a more efficient method (less searches of table to create array)

                    Code:
                    	dim shared MyCollection as U
                    	myCollection.initialize(vCollFormat,"1",vCollFormat,vCollText)
                    	dim vFilter as c = ""
                    	KeyVal = mycollection.first()
                    	for n = 1 to myCollection.size()
                    		CurrentVal = mycollection.get(KeyVal)
                    		if word(CurrentVal,4,"|") <> "" then
                    			vFilter = vFilter-"(reg='"-word(currentVal,1,"|")-"'.and.country='"-word(currentVal,4,"|")-"').or."
                    		else
                    			vFilter = vFilter - "reg='"-word(CurrentVal,1,"|")-"'.or."
                    		end if
                    		KeyVal = mycollection.next(KeyVal)
                    	next n
                    	dim vFilterLen as n = len(vfilter)
                    	dim vFilterTxt[0] as c
                    	if vFilterLen > 1024 then
                    		[COLOR="#0000CD"]dim shared vFilterData as c = rtrim(left(vFilter,1024))
                    		dim shared vFilterLen as n = len(vfilter)
                    		dim shared vDataLen as n = 1
                    		dim Pos as n = 1
                    		while vDatalen > 0
                    			vDataLen   = len(vFilterData)-len(word(vFilterData,-1,".or."))
                    			vFilterTxt[] = rtrim(substr(vFilter,Pos,vDatalen-4))
                    			pos = pos + vDatalen
                    			vFilterData = rtrim(substr(vFilter,pos,1024))
                    		end while
                    		vFilterTxt.resize(vFilterTxt.first_empty()-1)[/COLOR]
                    	else
                    	vFilterTxt[] = left(vFilter,len(rtrim(vFilter))-4)
                    	end if
                    	vOrder = "REG"
                    	dim shared vDisplayData[9999] as p
                    	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
                    	for n = 2 to vFilterTxt.size()
                    		vDisplayData.initialize_from_table("Logging",vFilterTxt[n],vOrder,.t.)
                    	next n
                    	vDisplayData.resize(vDisplayData.first_empty()-1)
                    I created one long filter string, then chopped it in the maximum number of conditions for each search (i.e. maximum of 1024 characters).
                    --
                    Support your local Search and Rescue Unit, Get Lost!

                    www.westrowops.co.uk

                    Comment

                    Working...
                    X