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

Field name in a variable

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

  • Field name in a variable

    I need a piece of code that will replace a Tables' subelement with a variable i.e. replace a field name with a variable.

    I have two Tables: Table 1 (tbls) "Seniors", Table 2 (tblt) "Telephonebook". Table 1 has 150 records of Forename, Surname and Telephone. Table 2 has 1 record with 450 fields - F1,S1,T1 ........... F150,S150,T150.
    I am trying to construct Table 2's Field names as a variable and then copy the contents of Table 1 to the Fields in Table 2.

    Code in English:-
    X = 0
    Go to the first record in the file Seniors
    Whilst not EOF Seniors
    x = x + 1
    construct 3 Fields F1, S1,T1
    Table "TelephoneBook" Field "F1" = Table "Seniors" Field "Forename"
    Table "TelephoneBook" Field "S1" = Table "Seniors" Field "Surname"
    Table "TelephoneBook" Field "T1" = Table "Seniors" Field "Telephone"
    Go to the next record in the Seniors file and construct 3 new fields using x
    Continue until the EOF Seniors
    The table Telephonebook will now contain 450 completed fields from F1,S1,T1......F150,S150,T150
    At the moment I have done this with 150 IF statements - not a pretty sight, but it works!
    Regards
    Ron


    The reward for work well done is more work!

  • #2
    Re: Field name in a variable

    Ron,

    Is not what you ask in contrary to the principles of a database.
    How can you keep all data up to date in only one record?
    What can you do with that one-record-table?

    For me, these questions block me to help you.

    Sorry, Maybe I am totally wrong and do not understand you.
    But if it is a only once job I would do it by hand with excel.

    Ton
    Most things are simple but unfortunately only after the first time

    Comment


    • #3
      Re: Field name in a variable

      Ton,

      I agree with you it could be done in Excel but it is really simple with a script. The base table is a simple Forename, Surname, Telephone No. A simple script takes this information and transfers it to this one record table. The table is used in a form to print an A5 telephone book. This book is constructed from one sheet of A4 card, printed both sides, folded vertically and then guillotined in half, thus making the A5 booklet. I have programmed it and it is instantaneous but as I said I do not like 'long-winded' code.

      Ron
      Regards
      Ron


      The reward for work well done is more work!

      Comment


      • #4
        Re: Field name in a variable

        Ron,

        unknown to your application I would suggest that you print the list from senior table. Then you can sort on name -if you like- before printing.

        I think you now have done a lot of work to make a report with all the different fieldnames and now to make a script transferring your records.
        IMHO you always must update your script and report after entering new records or deleting records from the senior table. Or do you update the telephone Book file? If you can?

        Can take a look at Alpha Sports application give you more knowledgde about what you can do with Alpha Five?

        Anyway, Welcom at Alpha's Forum. This is the place for discussion about almost everything about Alpha Five.

        Ton
        Most things are simple but unfortunately only after the first time

        Comment


        • #5
          Re: Field name in a variable

          Ron,
          This should do it:
          Code:
          t1=table.open("Seniors",file_ro_shared)
          t2=table.open("Telephonebook",file_rw_exclusive)
          t2.zap(.t.)
          
          t1.fetch_first()
          i=1
          while .not. t.fetch_eof()
          t2.enter_begin()
            eval("t2.f"+i)=t1.forename
            eval("t2.s"+i)=t1.surname
            eval("t2.t"+i)=t1.telephone
          t2.enter_end()
          i=i+1
          end while
          t1.close()
          t2.close()
          The eval() function lets you reference just about anything as a variable.
          - Peter

          Comment


          • #6
            Re: Field name in a variable

            Peter

            That worked - thank you very much.
            Am I allowed to ask another question - well here goes anyway. I have been using Superbase for 30 years and when I create a table with one of the fields indexed, it remains in that order and when I add a new record it automatically inserts the record in the correct order. When I add a record to an Alpha 5 table, it adds it to the end of the file. I can then sort the file/table but on closing it reverts to its previous order.
            What am I doing wrong?

            Regards

            Ron
            Regards
            Ron


            The reward for work well done is more work!

            Comment


            • #7
              Re: Field name in a variable

              Ton

              The file is in order, but only in blocks in two columns.

              Page 1 Page 2
              Fields [1-28] [141-168] Club Logo
              [57-84] [85-112] [113-140] [29-56]

              I now have the solution eval() does the job very well.
              Thanks for your help.

              Regards

              Ron
              Regards
              Ron


              The reward for work well done is more work!

              Comment


              • #8
                Re: Field name in a variable

                Ton

                Sorry about the previous posting, it stripped the blanks.
                The pages are in two columns:-

                Page 1
                Top of page Column 1 Fields [1-28] Column 2 Empty
                Bottom of page Column 1 [57-84] Column 2 [85-112]

                Page 2
                Top of page Column 1 Fields [141-168] Column 2 Club Logo
                Bottom of page Column 1 [113-140] Column 2 [29-56]

                I wish I could lay this posting out better. I have a 'layout' form that shows the diagram, but I am new and I do not know what files to attach to this posting.
                I now have the solution eval() does the job very well.
                Thanks for your help.

                Regards

                Ron
                Regards
                Ron


                The reward for work well done is more work!

                Comment


                • #9
                  Re: Field name in a variable

                  Hi Ron,

                  Since Alpha Five we call a file with data a table and all the tables together are called a database.

                  Normally a table is build on historical data entries.
                  In case the fields are e.g. firstname, lastname, date, telephone, city.
                  If you had sorted your table on lastname and you now would have a list on city and lastname you should resort the table.
                  Tomorrow you will have another sort and so on.
                  A table can have many records and sorting requires a lot of time then.
                  Take note that the table is not updatable while sorting.
                  Working with more users who frequently sort the table is a nightmare because every user wants his own sort.

                  So with indexes you can get the data in the order you've specified (in the indexbuilder).
                  If you want to have a list on city and lastname and date, define that as an index. You can have more than one index. Therefore there is no need to sort your data. Make use of an index for every wanted view. If you need the data on a form, a list or a browse just choose one of the indexen you've made.
                  Important to know is that indexes will be updated while updating the data.

                  Let Alpha Five manage the data, you take care of entries and updating so the data is usable any moment in any way you want.

                  I hope this helps to understand the basics.

                  Ton
                  Most things are simple but unfortunately only after the first time

                  Comment


                  • #10
                    Re: Field name in a variable

                    Ton

                    Thanks for that. Is there some way I can save the last index used, so that the table will always open in that order?

                    Regards

                    Ron
                    Regards
                    Ron


                    The reward for work well done is more work!

                    Comment


                    • #11
                      Re: Field name in a variable

                      In this script the purpose was to allow the user to search for the values of any one selected field in the inventory table. It runs on an "on Push" event for a button called btnFind on a form. The user selects first selects a field name from the inventory table and stores the name in Character type in the variable vSearchBy, then the user enters the value they wish to find in that field. Then the users selects the parameter for the search (i.e. - Exact Match, Contains Words Like, Contains Exact Words). Then the clicks the button labeled "Find in Table" and this script runs.

                      *Just another way to do it.

                      Code:
                      'On push for a "Find In Table" button on a menu.
                      'THe purpose is to search any fields in the inventory (arvint) table.
                      'The three values determine the fields in the inventory (arvint) table to search, the text to search for, and the parameters of the search (i.e. exact match etc)
                      
                      DIM vSearchBy as c '
                      DIM vFind as c
                      DIM vSearch as c
                      
                      vSearchBy = MainMenu:vsearchitem.value 'List of Fields in the Inventory (arvint) table.  Value of variable is the name of the selected field
                      vSearchBy = alltrim(vSearchBy)
                      
                      vFind = MainMenu:vsearchitemfor.value ' THe text to find, must be able to make ths value of this field have the data type of the field being searched.
                      vFind = alltrim(vFind)
                      
                      dim tbl as p 
                      'get a pointer to the primary table of the form (the inventory form
                      tbl = table.open("arinvt")
                      
                      'Dimension variables
                      DIM vFieldType as c
                      DIM vFieldNum as n
                      DIM vFieldList as c
                      DIM vField as p
                      dim filterExpression as c 
                      dim orderExpression as c 
                      
                      'get the field being searched into a pointer variable (vField) and also figure out what date type that the field selected is (vFieldType)
                      vFieldList = tbl.field_name_get()
                      vFieldNum = word_number_get(vFieldList,vSearchBy,crlf(),.t.)
                      vField = tbl.field_get(vFieldNum)
                      vFieldType = vField.type_get()
                      
                      'Show the field type...for debugging
                      'ui_msg_box("Field Type is...",alltrim(vFieldType))
                      
                      'Set the filter and order expressions based on what the data type is of the field being searched
                      ' Could do this also in a more simplified way using the convert_type() functions. So something like....
                      
                      ' ""+vSearchBy+" = convert_type('"+vFind+"','"+vFieldType+"')"     'Not Tested unsure of results  but would replace if then clauses
                      
                      'could also us Select Case Logic for each different field type for the fields in the inventory table
                      if vFieldType = "N" then
                      	filterExpression = ""+vSearchBy+"=val('"+vFind+"')"
                      	orderExpression = "Itemno"
                      else if vFieldType = "D" then
                      	filterExpression = ""+vSearchBy+"=ctod('"+vFind+"')"
                      	orderExpression = "Itemno"
                      else if vFieldType = "C" then
                      	filterExpression = "contains("+vSearchBy+",alltrim('"+vFind+"'))"
                      	orderExpression = "Itemno"
                      else
                      	ui_msg_box("Error","The search function only supports character, date, and numeric fields, not"+ " " + alltrim(vFieldType)+".  " + "type fields.  Please ask your programmer to add this feature if it is necessary")
                      	end 
                      end if
                      
                      dim i as p 
                      dim count as n 
                      'i = the remaining records in the inventory report after the filter and order
                      
                      i = tbl.order(orderExpression,filterExpression)
                      count = i.records_get()
                      
                      ' If we found no records, then we put up a warning and show all records
                      if count = 0
                      	ui_msg_box("Warning", "No records found. Showing all records")
                      
                      	filterExpression = vSearchBy + "<>" + quote("")
                      	orderExpression = "Itemno"
                      	i = tbl.order(orderExpression,filterExpression)
                      	count = i.records_get()
                      	if count = 0 then 
                      		topparent.allow_change(.f.)
                      	end if 
                      
                      	mulstbtn1.text = "1"  '' I chose '1' because it is not in the set A-Z that we display 
                      end if
                      
                      DIM varP as p
                      'show results from the inventory table in a browse view where the user can double click the record to view the inventory record details
                      
                      varP = :browse.ViewQueried("brwInv_Ar",filterExpression,orderExpression)
                      Lee Goldberg
                      Software Development and Sales
                      Shore Consultants Ltd.
                      [email protected]
                      www.shorecon.com

                      Comment


                      • #12
                        Re: Field name in a variable

                        Lee

                        Thank you for that.
                        How did you do that scrolling 'frame' with the code?

                        Regards

                        Ron
                        Regards
                        Ron


                        The reward for work well done is more work!

                        Comment


                        • #13
                          Re: Field name in a variable

                          Ron,
                          As Ton pointed out, an important concept is that a form or browse is just a "view" of the records in the table....and as such can be sorted/filtered to present different ways of looking at the data.

                          If you want a form or browse to always be sorted/filtered ALWAYS, then use the form properties to do this...it sets a base filter that cannot be dropped unless specifically coded to do so....Or use the index method and set the index upon viewing the records....Or set the filter in the OnInit or OnActivate events of the form/browse...

                          So many ways!! :)

                          ----the use of the Code Tags in the advanced reply part of the post process will keep code formatting and add the scrolling you see. Just highlight the code and click on the Code Tag icon #
                          Mike
                          __________________________________________
                          It is only when we forget all our learning that we begin to know.
                          It's not what you look at that matters, it's what you see.
                          Henry David Thoreau
                          __________________________________________



                          Comment

                          Working...
                          X