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

  • Graham Wickens
    replied
    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).

    Leave a comment:


  • Graham Wickens
    replied
    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?

    Leave a comment:


  • Graham Wickens
    replied
    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.

    Leave a comment:


  • MoGrace
    replied
    Re: Problem with queries

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

    Leave a comment:


  • Ted Giles
    replied
    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.

    Leave a comment:


  • Graham Wickens
    replied
    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()

    Leave a comment:


  • MoGrace
    replied
    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.

    Leave a comment:


  • madtowng
    replied
    Re: Problem with queries

    Hi Graham.

    How are you generating the list of 1000 records (do you have that many columns you are checking) ?

    Leave a comment:


  • Ted Giles
    replied
    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?

    Leave a comment:


  • Graham Wickens
    started a topic Problem with queries

    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
Working...
X