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

Using a Record list -List box for selections of more than one field at a time.

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

    Using a Record list -List box for selections of more than one field at a time.

    I am in over my head on this one and could sure use some guidance. I used the Action Scripting method to develop the code below, which works perfectly to display the 8 fields from a filtered table and display them in a list box allowing me to pick a table record from a row of displayed fields in that record. Once the choice is made, the List Box then stores the data from 1 of the tables fields (Type) in a variable (Dvc_type) for my later use.

    My problem is, how do I modify this script to store every one of the displayed fields data from the line I have chosen, into their own separate variables. i.e. Make stored to dvc_make and size stored to dvc_size etc.

    I hope I am making sense here and thanks for any help offered !!

    Here is the code I have working for one field and one variable:

    'Create an XDialog dialog box to prompt for parameters.
    DIM SHARED dvc_type as C
    DIM SHARED varC_result as C
    ok_button_label = "&OK"
    cancel_button_label = "&Cancel"
    DIM dvc_type_rl_def as C
    dvc_type_rl_def = "kl=devices,{keylist_build(\"H=.05,1:15[Testable],2:15[Type],3:25[Make],4:9[Size],5:25[Model],6:25[Serial],7:25[Location],8:27[Cross_Con],9:15[Hazard]\",''+type,left(''+testable,15),left(''+type,15),left(''+make,25),left(''+size,9),left(''+model,25),left(''+serial,25),left(''+location,25),left(''+cross_con,27),left(''+hazard,15))}{Cust_Numb=Var->cust2}"
    varC_result = ui_dlg_box("Previous Devices",<<%dlg%
    {region}
    Choose Device:| [%@dvc_type_rl_def%.140,5dvc_type];
    {endregion};
    {line=1,0};
    {region}
    <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
    {endregion};
    %dlg%)

    #2
    Re: Using a Record list -List box for selections of more than one field at a time.

    Hi Mike,
    This link tbl.record_data_get() might help you get started.
    Robin

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

    Comment


      #3
      Re: Using a Record list -List box for selections of more than one field at a time.

      Someone's more likely to take a look if you attach the table.
      There can be only one.

      Comment


        #4
        Re: Using a Record list -List box for selections of more than one field at a time.

        Here is the table I should have attached earlier.
        Attached Files

        Comment


          #5
          Re: Using a Record list -List box for selections of more than one field at a time.

          Thank you for the link and I have used this method of getting records before, but I don't think quite fits my needs this time around, unless I do a lot of coding :-) But I am going to study that method a little further. I am using the list box to pick just a certain record from a filtered list of records from the table, so if I could use the generated script I posted, but just add a few more variables, it would be perfect!

          Thanks again!!

          Comment


            #6
            Re: Using a Record list -List box for selections of more than one field at a time.

            You are sooo right Stan! Posted it and thanks!

            Originally posted by Stan Mathews View Post
            Someone's more likely to take a look if you attach the table.

            Comment


              #7
              Re: Using a Record list -List box for selections of more than one field at a time.

              Mike,

              "Type" is a reserved word. I recommend you do not use reserved words for objects, including table fields, in your application.

              -- tom

              Comment


                #8
                Re: Using a Record list -List box for selections of more than one field at a time.

                Didn't realize that "Type" was on of those. Thanks Tom, great catch. I will be changing that in a few tables right now! In fact I was having trouble trying to use a conditional look up in field rules and that may have been an issue...

                Originally posted by Tom Cone Jr View Post
                Mike,

                "Type" is a reserved word. I recommend you do not use reserved words for objects, including table fields, in your application.

                -- tom

                Comment


                  #9
                  Re: Using a Record list -List box for selections of more than one field at a time.

                  Hi Tom,
                  The Wiki only mentions that TYPE should not be used as a variable in a script. I have several tables that have a field named TYPE and have not had problems.

                  Reserved Words
                  Robin

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

                  Comment


                    #10
                    Re: Using a Record list -List box for selections of more than one field at a time.

                    We create the list a little differently. Instead of building a list box, we create an array of the field data we want, then after the array is displayed and an appropriate record is selected, we parse the selected data and put each piece into it's own variable.
                    in this example, we're using the client's ssn to find all cases associated with that client, then displaying the required fields separated by a "~" as an array for the worker to choose which case number they want to use to create a record in another table. Once the array record is built (manually), it can be parsed using the word() function and the "~" as a field separator and each piece can be assigned to a separate variable. (it's not the cleanest code in the world, but it works for us)

                    Code:
                    		'display a list box of cases ending with "last6"
                    		DIM Cases[15] as C
                    		DispAgain:
                    		'debugcheck()
                    		cases.clear()
                    		DIM i as N = 1
                    		DIM Case_select as C = lnk.case
                    		DIM lnk1 as P
                    		dim clt1 as P
                    		dim vFilefolder as C = ""
                    		lnk1 = table.open("Link",FILE_RO_SHARED)
                    		clt1 = table.open("client",FILE_RO_SHARED)
                    		clt1.index_primary_put("SSN")
                    		lnk1.index_primary_put("SSN")
                    		ans6 = lnk1.fetch_find(vSSN)
                    		if ans6 >0
                    			if lnk1.pgm = "SVC" .or.left(lnk1.pgm,2)="RC" .or. "Do Not"$lnk1.action2
                    					'skip these cases
                    			  else
                    				cltok = clt1.fetch_find(lnk.ssn)
                    				if cltok >0
                    					if alltrim(lnk1.filefolder) = ""
                    						vFilefolder = lnk1.case
                    					  else
                    					  	vfilefolder = lnk1.filefolder
                    					end if
                    					if left(lnk1.case,4)="0270"
                    						cases[i] = vfilefolder+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+trim(clt1.fname)+" ~ "+trim(clt1.lname)+" ~ " + "("+lnk1.case+")"
                    						ACA_case = lnk1.case
                    					  else
                    					  	cases[i] = lnk1.case+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+" ~ "+trim(clt1.fname)+" ~ "+trim(clt1.lname)
                    					end if
                    				end if
                    				i = i+1
                    			end if
                    			lnk1.fetch_next()
                    			while lnk1.ssn = vSSN
                    				if alltrim(lnk1.filefolder) = ""
                    					vFilefolder = lnk1.case
                    				  else
                    				  	vfilefolder = lnk1.filefolder
                    				end if
                    				if lnk1.pgm = "SVC" .or.left(lnk1.pgm,2)="RC" .or. "Do Not"$lnk1.action2
                    					'skip these cases
                    				  else
                    				  	cltok = clt1.fetch_find(lnk.ssn)
                    					if cltok >0
                    						if left(lnk1.case,4)="0230"
                    							cases[i] = vfilefolder+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+" ~ "+trim(clt1.fname)+" ~ "+trim(clt1.lname)+" ~ " + "("+lnk1.case+")"
                    						  elseif left(lnk1.case,4)="0270"
                    							cases[i] = vfilefolder+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+" ~ "+trim(clt1.fname)+" ~ "+trim(clt1.lname)+" ~ " + "("+lnk1.case+")"
                    							ACA_case = lnk1.case
                    						  else
                    						  	cases[i] = lnk1.case+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+" ~ "+trim(clt1.fname)+" ~ "+trim(clt1.lname)
                    						end if
                    					  else
                    						if left(lnk1.case,4)="0230"
                    							cases[i] = vfilefolder+" ~ "+lnk1.pgm+" "+lnk1.SSN+" ~ "+trim(clt1.fname)+" ~ "+trim(clt1.lname)+" ~ " + "("+lnk1.case+")"
                    						  elseif left(lnk1.case,4)="0270"
                    							cases[i] = vfilefolder+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN+" ~ "+ "("+lnk1.case+")"
                    							ACA_case = lnk1.case
                    						  else
                    						  	cases[i] = lnk1.case+" ~ "+lnk1.pgm+" ~ "+lnk1.SSN
                    						end if
                    					end if
                    					i = i+1
                    				end if
                    				vfilefolder = ""
                    				lnk1.fetch_next()
                    			end while
                    			end if
                    			clt1.close()
                    			lnk1.close()
                    			delete vFilefolder
                    			DIM SHARED vCasenew as C
                    			lastone = cases.first_empty()-1
                    			if lastone = 1
                    				'no need to select a case, just take the first one
                    				vCasenew = cases[1]
                    				goto bypass1
                    			end if
                    			cases.resize(lastone)
                    			cases.sort("A","word(value,2)+word(value,1)")
                    			DIM SHARED varC_rslt1 as C
                    			Delete XdialogStyle
                    			dim XDialogStyle as p
                    			XDialogStyle.AccentColor = "#255+255+234"
                    			XDialogStyle.Color = "#254+254+193"
                    			heading_str1 = "Please Select the Appropriate Case Number"
                    			footer_str1 ="If there is NO Name, DO NOT Use the case"
                    			varC_rslt1 = ui_dlg_box(agency,<<%dlg%
                    			{Windowstyle=Gradient Horizontal Top}		
                    			{region}
                    			{text=45,1:heading_str1};
                    			{endregion};
                    			{region}
                    			Select the proper case:| [.60,10vCasenew^#cases];        
                    			{endregion};
                    			{line=1,0};
                    			{region}
                    			{text=45,1:footer_str1};
                    			{endregion};
                    			{line=1,0};
                    			{region}
                    			<*15OK> <15CANCEL>
                    			{endregion};
                    			%dlg%)
                    			if varC_rslt1 <> "OK" 
                    				ui_msg_box(agency,"Action Cancelled at user request")
                    				goto cleanup
                    			end if
                    			testcase = alltrim(word(vCasenew,4,"~"))+" "+alltrim(word(vCasenew,5,"~"))
                    			if cltname <> testcase
                    				anstest=ui_msg_box(agency,"Name of Selected Case: "+testcase+" does not match this client: "+cltname+crlf(2)+"Is this the case you REALLY Want?",292)
                    				if anstest <>6
                    					ui_msg_box(agency,"Please select again")
                    					goto Dispagain
                    				end if
                    			end if
                    ... a cleaner, easier method would be:

                    Code:
                    DIM SHARED selected as C
                    DIM SHARED varC_result as C
                    DIM vSSN as C = "---------"   'SSN removed to protect the innocent
                    filt = replace_parameters("ssn=[varC->vSSN]",local_variables())
                    auto_list_selected = table.external_record_content_get("link","Case+\"~\"+Pgm+\"~\"+Ssn+\"~\"+Date1","pgm",filt)
                    
                    temp_count = w_count(auto_list_selected,crlf())
                    DELETE a_selected
                    DIM a_selected[temp_count] as c
                    a_selected.initialize(auto_list_selected)
                    ok_button_label = "&OK"
                    cancel_button_label = "&Cancel"
                    varC_result = ui_dlg_box("Alpha Five",<<%dlg%
                    {region}
                    please select:| [.60,5selected^#a_selected];
                    {endregion};
                    {line=1,0};
                    {region}
                    <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
                    {endregion};
                    %dlg%)
                    The selected line is presented in the variable "Selected". It can then be parsed to choose each field and assign it to its own variable.

                    Tom
                    Last edited by Tom Henkel; 10-29-2015, 12:58 PM. Reason: a cleaner method

                    Comment


                      #11
                      Re: Using a Record list -List box for selections of more than one field at a time.

                      Mike,

                      dvc_type_rl_def = "kl=devices,{keylist_build(\"H=.05,1:15[Testable],2:15[Type],3:25[Make],4:9[Size],5:25[Model],6:25[Serial],7:25[Location],8:27[Cross_Con],9:15[Hazard]\", ''+type, left(''+testable,15),left(''+type,15),left(''+make,25),left(''+size,9),left(''+model,25),left(''+serial,25),left(''+location,25),left(''+cross_con,27),left(''+hazard,15))}{Cust_Numb=Var->cust2}"


                      varC_result = ui_dlg_box("Previous Devices",<<%dlg%
                      {region}
                      Choose Device:| [%@dvc_type_rl_def%.140,5dvc_type];
                      .....

                      'Add button event
                      Code:
                      'The Keybuild list has the ID and find it with Id.
                      if a_dlg_button = "ok" then
                          'only one selection case
                          filter = "your_field_id = '"+dvc_type+"'"          'or  filter = "your_field_id = "+quote(dvc_type)
                          'using lookup() to get a field value
                          vMake = lookup("yourtablename",filter,"your_field_name")
                          'Now you have the "vMake" 's value.  save it to your new table or ...
                       
                      end if

                      Comment


                        #12
                        Re: Using a Record list -List box for selections of more than one field at a time.

                        I am not sure your code will fully provide what I am after. But it would help if I understood the code I had provided in the first place, which I don't.

                        I know the code I provided works great at extracting one field from a record, out of a filtered external(not linked) table and storing that field's value into a variable. I need to store all data from 8 of the fields in that record to 8 different variables.

                        What I will be doing is placing this code in a field's ondepart event or on an Onarrive event so that it pops up upon arrival at the field on a form. Then a list of records from an external table (devices) will appear in the Pick list that is populated (filtered) to match the customer in the record I am adding. There could be many matching records in the device table, but I need to pick one record of my choice and store the results of the 8 fields in that record into 8 variables to use to auto-populate some fields further down on the form.

                        And thanks again for everyone's time looking at this, I suspect I have done a terrible job explaining what I am trying to do, of course it is clear to me. :-)

                        Originally posted by johnkoh View Post
                        Mike,



                        Code:
                        'The Keybuild list has the ID and find it with Id.
                        if a_dlg_button = "ok" then
                            'only one selection case
                            filter = "your_field_id = '"+dvc_type+"'"          'or  filter = "your_field_id = "+quote(dvc_type)
                            'using lookup() to get a field value
                            vMake = lookup("yourtablename",filter,"your_field_name")
                            'Now you have the "vMake" 's value.  save it to your new table or ...
                         
                        end if

                        Comment


                          #13
                          Re: Using a Record list -List box for selections of more than one field at a time.

                          Mike,

                          but I need to pick one record of my choice and store the results of the 8 fields in that record into 8 variables to use to auto-populate some fields further down on the form.
                          Code:
                          'Button event
                          'The Keybuild list has the ID and find it with Id.  <===[SIZE=3][B][COLOR="#FF0000"] " I need to pick one record of my choice "[/COLOR] [/B][/SIZE]
                          if a_dlg_button = "ok" then
                              'only one selection case
                              filter = "your_field_id = '"+dvc_type+"'"          'or  filter = "your_field_id = "+quote(dvc_type)
                              'using lookup() to get a field value
                              vMake = lookup("yourtablename",filter,"your_field_name")
                              'Now you have the "vMake" 's value.  save it to your new table or ...
                          
                          [SIZE=3][B][COLOR="#FF0000"]" store the results of the 8 fields in that record into 8 variables "[/COLOR][/B][/SIZE]
                          'You have to set the variable value. The KeyBuild_list has only 1 variable return, not all of 8 variables. it will not know the other variables.
                           
                          'Find the field's value with Table or Table.external...() method with the "Key_Id".
                          tbl = table.open("yourTableName")
                          query.filter ="YourFieldName="+quote( "pick one record of my choice ")  ' filter = "your_field_id = '"+dvc_type+"'"          'or  filter = "your_field_id = "+quote(dvc_type)
                          query.order ="" 
                          qry = tbl.query_create()
                          count = indx.records_get()
                          If count > 0 then
                              [B][COLOR="#FF0000"][SIZE=3]8 variables to use to auto-populate some fields [/SIZE][/COLOR][/B] ==> you have to save it.
                          
                              'open 2nd/ your destination table
                              tDest = table.open("Dest_table")
                              tDest.enter_begin()
                                    tDest.field_1 = tbl.SourceField
                                    'repeating as your designed
                              tDest.enter_end(.t.)
                              tDest.close()
                          
                             'or apply it on your form fields
                              parentform:yourfield_name.value = tbl.SourceField_1
                              parentform:yourfield_name.value = tbl.SourceField_2
                              Parentform: ' repeating ...
                          end if
                          tbl.close()
                          
                          vTemplist = table.external....("yourtable","..., filter)
                          'read the list and save to table
                          for each foo in vTemlist
                               var_1 = word(foo.value,1,crlf())
                               var_2 = word(foo.value,1,crlf())
                               'Repeating .... 'until 8th variables
                               'apply the table method ('open the table and save) 
                               
                          
                            'or apply it on your form fields
                              parentform:yourfield_name.value = word(foo.value,1,crlf())
                              parentform:yourfield_name.value = word(foo.value,2,crlf())
                              Parentform: ' repeating ...
                          
                          next
                          end if
                          Last edited by johnkoh; 10-29-2015, 04:24 PM. Reason: wrong code at loop

                          Comment


                            #14
                            Re: Using a Record list -List box for selections of more than one field at a time.

                            Mike,

                            May be this?
                            Attached Files

                            Comment


                              #15
                              Re: Using a Record list -List box for selections of more than one field at a time.

                              John you have given me some great examples! The recent xbasic code appears to be pretty close to where I need to be going I will study it more tomorrow and see if I can figure out where my values such as table names and field names need to be plugged into the code. Since I am looking only at placing the acquired field values into variables, I suspect I will have to discard the other code you were kind enough to include for saving to a table or directly writing to my form. It is a great start, even if I am still a little confused. I at least am heading the right way.

                              The variable values of the acquired 8 table fields will be inserted into the fields on my form conditionally, using a calculated expression in the field rules default entry area.

                              Thanks !!

                              Comment

                              Working...
                              X