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

Basic Append Question

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

  • #16
    Re: Basic Append Question

    since the record is not deleted yet, you would need to do it after it was deleted
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    [email protected]

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

    Comment


    • #17
      Re: Basic Append Question

      This is what I have working so far:

      Code:
      FUNCTION Auto_Lup AS L (fname as C,tname = "" as C)
      'DESCRIPTION: Used in FRUL CanWrite field event to get next field value for primary key
      'and in CanDeleteRecord record event to test if deletion is allowed.
      '
      'Use this function instead of FRUL calc Auto Increment so if needed in the future
      'the series for a primary key can be changed using the Counter table, eg. for Invoices
      'that use a new prefix to reflect the year - allows user to reset the series.
      '
      'Ex1: CanWrite event in DIR_ID field - Auto_Lup("DIRCODE")
      '-----User enters a new Directory->DIR_ID value and function gets the next DIRCODE value
      '
      'Ex2: CanDeleteRecord 
      'flag = Auto_Lup("DIRCODE")
      'if .not. flag then
      '	cancel()
      'end if
      'function allows deletion of last record entered only in case of user error and restores
      'the next number to its previous value so series will not be interrupted.
      
      	dim lv as p = local_variables()
      	dim commit_flag as L = .f.
      	dim fld as p
      	dim tc as p
      	dim tp as p
      	dim tpname as c = "counter"
      	dim msg as c
      	dim tries as n = 0
      	dim vmax as c
      	
      	tc = table.current()
      	if tname = "" then
      		tname = tc.name_get()		
      	end if
      	fld = tc.field_get(fname)	
      	vmax = alltrim(tablemax(tname,".t.",fname))
      				
      	If tc.mode_get() = 2 Then	'current table is in ENTER mode	
      		TRYAGAIN:
      		If tries < 4 Then			
      			Dim newval as C	
      			commit_flag = .t.					
      			On Error goto ERRLOOP 
      			tp = table.open(tpname,FILE_RW_EXCLUSIVE)
      			On Error goto 0
      			tp.index_primary_put("tblname")									
      			rec = tp.fetch_find(tname)
      			if rec > 0 then
      				newval = ut(tp.nextval)
      			else
      				msg="COUNTER record not found for table: "+ut(tname)
      				Tln("Auto_Lup",msg)
      				'trace.WriteLn("Auto_Lup: "+msg)
      				msg=crlf(2)+msg+"Please report error to Admin and cancel new record"+crlf()				
      				msgbox("Auto_Lup() Script Error",msg,16)
      				msg = ""
      			end if
      			
      			If fld.type_get() = "N" Then	'current field type should match tp.type, can use either here
      				fld.value_put(val(newval))
      			Else
      				fld.value_put(newval)
      			End If		
      										
      			on error goto CLOSETP
      			tp.change_begin()
      				If tp.type = "N"
      					tp.lastval = val(newval)
      					tp.preval = val(vmax)
      				Else
      					tp.lastval = newval
      					tp.preval = vmax
      				End If
      			CLOSETP:
      			tp.change_end(.t.)				
      			tp.close()			
      		End If
      	Else If A_DELETING_RECORD = .T. Then
      		'Allow or cancel deletion of the current record in CanDeleteRecord FRUL event
      		Auto_Lup = Del_Rec(tname,lv)				
      		EXIT FUNCTION	
      	End If
      	
      	Auto_Lup=commit_flag
      	Tln(ut(tname)+"-New Record Added",ut(fname)+": "+newval)
      	'trace.WriteLn(ut(tname)+"-New Record Added -"fname+": "+newval)
      	end 
      	
      	ERRLOOP: 
      	tries = tries + 1
      	if tries < 4 then		
      		RESUME 0
      	else
      		msg = "You do not have exclusive access to the "+ut(tname)+" table."
      		Tln("Auto_Lup",msg)
      		'trace.WriteLn("Auto_Lup: "+msg)
      		msg = crlf()+msg+"Please CANCEL the new record else check that no other users"+crlf()
      		msg = msg+"are using the table and try again."+crlf(2)
      		msg = msg+"If this error continues, then try one or all of the following:"+crlf()
      		msg = msg+"1. Close all open forms"+crlf()
      		msg = msg+"2. Exit then reopen the application"+crlf()
      		msg = msg+"3. Contact Admin and report this error for additional options to try."+crlf(2)
      		msg = msg+"Select OK to retry or CANCEL to cancel your new record"
      		
      		response = msgbox("New Record Locked: Table in Use",msg,49)
      		msg = ""
      		if response = 2 then
      			'user cancelled
      			cancel()
      		else
      			tries = 1			
      			goto TRYAGAIN		
      		end if
      	end if	
      END FUNCTION
      
      function Del_rec AS L (tname as c,LocVars as p)		
      	WITH LocVars	
      	dim curval as c
      	dim oldval as c
      	commit_flag = .f.
      	curval = fld.value_get()	
      	on error goto ERRMSG	
      	tp = table.open(tpname,FILE_RW_EXCLUSIVE)
      	On Error goto 0	
      	tp.index_primary_put("tblname")	
      	tp.fetch_find(tname)
      	oldval = tp.preval
      	if tp.lastval = curval then				
      		commit_flag = .t.
      		on error goto CLOSETP2			
      		tp.change_begin()
      			tp.lastval = oldval
      			tp.preval = Get_Prev(oldval)
      		CLOSETP2:
      		tp.change_end(commit_flag)				
      		tp.close()
      	end if		
      	
      	if commit_flag = .f. then					
      		msg = "Auto Increment rule is in force"+crlf()
      		msg = msg+"You may only delete the last record entered"+crlf(2)
      		msg = msg+"If this is in error, please see Admin to correct" 
      		msgbox("Field Rule Violation",msg,16)
      		msg = ""			
      	end if	
      	
      	Tln(ut(tname)+"-Record Deleted-"+ut(fname)+": "+curval,commit_flag)
      	'Trace.WriteLn(ut(tname)+"-Record Deleted-"+ut(fname)+": "+commit_flag)
      	
      	'sets the result of the calling script to test in CanDeleteRecord FRUL event	
      	Del_rec = commit_flag
      	goto ENDIT
      		
      	end
      	ERRMSG: 
      	commit_flag = .f.
      	script_play("ERRMSG")
      	on error goto 0
      	RESUME NEXT
      	
      	ENDIT:
      	END WITH
      end function
      
      function Get_Prev AS C(vc as C)
      'get the Counter->preval if deleting the current record by testing for the prefix in use
      'then decrements the value portion and puts the prefix back	
      'my 14 tables that use this UDF use 1 of 3 different prefix styles for their primary keys
      	dim va as c
      	dim vn as n
      	dim vs as n
      	'In counter table all values are type 'C'
      	if .not. isdigit(left(vc,1)) then		'D0115 DIRCODE; also KO_NO,STYLE_KEY,YARN_ID,HELPID,MSG_ID.
      		vs = len(alltrim(vc))				'5
      		va = left(vc,1)					'D
      		vc = alltrim(substr(vc,2))			'0115
      		vs = vs-1						'4
      		if .not. isdigit(left(vc,2)) then	        'YR-100007 REC_HDR (only exception to prefix)
      			va = substr(vc,1,3)			'YR-
      			vc = alltrim(substr(vc,4))		'100007
      			vs = vs-3					'9-3=6
      		end if					
      		vn = val(vc)-1					'115-1; 100007-1		
      		vc = va+padl(alltrim(str(vn)),vs,"0")'D+0+114; YR-+100006 (no pad needed)
      	else
      		vn = val(alltrim(vc))-1		        '165323-1 PS_NO; also SCH_NO,MACHNO,IMGNO,INV_NO,PMT_ID
      		vc = str(vn)				        '165322
      	end if
      	Get_Prev=vc
      end function
      My counter table follows this format: (Nextval is a calc field)
      counter_tbl.jpg
      Last edited by MoGrace; 07-28-2015, 11:47 AM.
      Robin

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

      Comment


      • #18
        Re: Basic Append Question

        Have you thoroughly tested it, yet? Please let us know.
        Cole Custom Programming - Terrell, Texas
        972 524 8714
        [email protected]

        ____________________
        "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

        Comment


        • #19
          Re: Basic Append Question

          An anomaly with Tablemax() ? lvlcls is a character field with numeric data - the first example is wrong but the 2nd is correct - why doesn't the first work right?

          ?tablemax("menux_img",".t.","lvlcls")
          ="9 "

          ?alltrim(str(tablemax("menux_img",".t.","val(lvlcls)")))
          = "59"

          Edit: because this is the order when I sort the browse on that field
          1
          10
          11
          12

          more work to do...
          Last edited by MoGrace; 07-28-2015, 02:28 PM.
          Robin

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

          Comment


          • #20
            Re: Basic Append Question

            tablemax() with a numeric field or tablemax() with a character field?
            There can be only one.

            Comment


            • #21
              Re: Basic Append Question

              Character fields evaluate left to right. "9 " is greater than "59".

              ? lst
              = 12
              99
              36100
              105
              1000
              100000

              ? sortsubstr(lst,crlf())
              = 1000
              100000
              105
              12
              36100
              99
              There can be only one.

              Comment


              • #22
                Re: Basic Append Question

                You need to pad the return value of the tablemax().

                tablemax("menux_img",".t.","padl(alltrim(str(val(lvlcls),19,0)),19,\"0\")")

                so the evaluated values are of the same length.

                Then "unpad it".

                alltrim(str(val(tablemax("menux_img",".t.","padl(alltrim(str(val(lvlcls),19,0)),19,\"0\")"))))

                If you padded the lvlcls in the table itself you wouldn't have the problem.
                Last edited by Stan Mathews; 07-28-2015, 02:45 PM.
                There can be only one.

                Comment


                • #23
                  Re: Basic Append Question

                  my main question is whether your use of canwrite in the field events in field rules will work the way you think it will. In that event I think you are referencing a value in the buffer, not the field, usually referred to as a_field_value.value (which I think is always a character value)
                  Cole Custom Programming - Terrell, Texas
                  972 524 8714
                  [email protected]

                  ____________________
                  "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

                  Comment


                  • #24
                    Re: Basic Append Question

                    Stan - my 2nd result in the IW (post #19) did return the data I wanted ("59").
                    I can't use a numeric field for LVLCLS because I have to format another field used for a Tree as "LVLCLS+LVLOPN" ("2+22")
                    My purpose in getting the last LVLCLS is for when an image is added to the table. Whereas the IMGNO field which is padded with '0' can be changed for sorting or grouping the list without changing the unique primary key. Thus the user selects IMGNO from a list and gets the correct LVLCLS record. This way an image can be changed, the sort order can be reorganized and the Tree will display the change correctly.

                    Martin - Since this function is most often used for adding new records, I don't actually need the new DIR_ID value the user is entering in order to populate the DIRCODE field. I only need him to be in that field, which is why I am using the CanWrite event for the DIR_ID field and testing for ENTER mode on the table in the function. Once the new record is saved, the DIR_ID field cannot normally be edited by the user. This is however the lookup field used by other tables when selecting a customer. If for some reason he does want to change the DIR_ID field, my sets will not be affected since they use the DIRCODE field.

                    These primary keys are being populated 'behind the scene' when a user creates a new record. The user doesn't normally see these values - exceptions being Inv_no, Ps_no and Yarn_id which have no natural keys.
                    Last edited by MoGrace; 07-28-2015, 03:53 PM.
                    Robin

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

                    Comment


                    • #25
                      Re: Basic Append Question

                      I have my two functions Auto_Lup and Reset_Counter working with one small problem - if the user enters a value and then cancels the new record the counter table has already been updated. I would need to use the Reset_Counter function somewhere - but there is no event in field rules that tests for if a record entry is cancelled.

                      Any suggestions?

                      Edit: If the user cancels before leaving the target field, no update occurs to the counter table. Perfect.
                      Otherwise if I use the OnWrote field event to save the record, then he must delete the record and my script is invoked in the CanDeleteRecord record event. Whew... thanks for reading.
                      Last edited by MoGrace; 08-03-2015, 11:53 AM.
                      Robin

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

                      Comment

                      Working...
                      X