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
Devcon 2020 Goes Virtual! LEARN MORE >

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

Update Tables by %

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    Update Tables by %

    Hi All,

    I would like some advise on how to update a rate table, that would involve as little work for the operators,

    I have a ratetable berates with the following fields

    Account char
    Prod Char
    Type Char
    Rzone1 Numeric
    Rzone2 Numeric
    .....
    Rzone10 Numeric

    This table has over 100 accounts in it with 86000 records,

    Here is what I would like the flow of the operation to be

    Pick accounts to be update from a check box list
    select services to be updated "ND" or "GF"
    insert into boxes or AskVars % to be update for each Rzone
    System updates selected Accounts and services

    how would you all go about this task

    one other thing I would like in an Ideal world is to be able to put in a date that these need to be updated

    IE Somehow to be able to set all these a week/Month before they are due to have the rate increase then have a prosses that runs to update all rates that are due to be updated today

    has anybody any ideas how to do this

    Andy
    Tags: None
    #2
    Re: Update Tables by %

    Hi Guys

    I have had a re think and have come up with another Idea

    if I have a table with dates account and increase amounts I can use a lookup to get the vars that I need, as code below

    Code:
    'Date Created: 14-Feb-2006 08:40:38 AM
'Last Updated: 25-Nov-2006 12:24:37 PM
'Created By  : Andy Meer
'Updated By  : Andy Meer


dim rinc1 as n
dim rinc2 as n
dim rinc3 as n
rinc1 = 0
rinc2 = 0
rinc3 = 0
service = ""
accountup = ""
dateup = date()
accountup = lookup("account_to_update","dateup =  date()","account")
service = lookup("account_to_update","account = '"+Var->accountup+"'","service")
rinc1 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone1")
rinc2 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone2")
rinc3 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone3")
rinc4 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone4")
rinc5 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone5")
rinc6 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone6")
rinc7 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone7")
rinc8 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone8")
rinc9 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone9")
rinc10 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone10")


a_tbl = table.open("beratetest")

DIM a5_operation_order as C
a5_operation_order = "recno()"
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = "account = '"+Var->accountup+"' .and. service = '"+Var->service+"'" 
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")

update.fields = 10
update.field1 = "RZONE1"
update.expr1 = "RZONE1*"+var->rinc1
update.field2 = "RZONE2"
update.expr2 = "RZONE2*"+var->rinc2
update.field3 = "RZONE3"
update.expr3 = "RZONE3*"+var->rinc3
update.field4 = "RZONE4"
update.expr4 = "RZONE4*"+var->rinc4
update.field5 = "RZONE5"
update.expr5 = "RZONE5*"+var->rinc5
update.field6 = "RZONE6"
update.expr6 = "RZONE6*"+var->rinc6
update.field7 = "RZONE7"
update.expr7 = "RZONE7*"+var->rinc7
update.field8 = "RZONE8"
update.expr8 = "RZONE8*"+var->rinc8
update.field9 = "RZONE9"
update.expr9 = "RZONE9*"+var->rinc9
update.field10 = "RZONE10"
update.expr10 = "RZONE10*"+var->rinc10

'Prompt for confirmation before running the Operation.......
dim rec_count as n
rec_count = a5_get_records_in_query("beratetest",a5_operation_filter,-1,.f.)
message_text = rec_count + " record(s) from 'beratetest' will be updated." + accountup +crlf(2)+ "OK to proceed?"
operation_result=ui_msg_box("Update Operation",message_text,UI_OK_CANCEL+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
If operation_result <> ui_ok_selected then
	end
end if


a_tbl.update()


GOTO CONTINUE13022006192837640
ERROR13022006192837640:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE13022006192837640:
a_tbl.close()
    This code works and updates my rate table.

    Could anybody tell me how I would then get the script to go to the next account in the lookup table

    Andy

    Comment

      #3
      Re: Update Tables by %

      Andy,
      Pick accounts to be update from a check box list
      How about a multi-selct Xdialog pulling the accounts from the table. The filter for generating would be in the table.external_record_content_get() function. You'll have to direct the function to the correct table, field (in red).

      Since you now have your variables (RINC 1 - 10) for the table values to amend, the script would cycle through pulling all the accounts chosen multi select list to update. IF YOUR ACCOUNT FIELD IS NOT NUMERIC, I don't know how this will work.

      Code:
      [COLOR="Blue"]'Create an XDialog dialog box of the account records in the table[/COLOR]	DIM Shared vListCt as N
	DIM SHARED test as C
	DIM SHARED varC_result as C
	DIM shared AccPick as C=""

[COLOR="Blue"]' The get number of records in selection list[/COLOR]
acc_list_tmp = table.external_record_content_get("[COLOR="Red"]client","Clientid[/COLOR]","","")

[COLOR="blue"]' The get number of records in selection list and generate Multi select Xdialog[/COLOR]	
      vListCt = w_count(acc_list_tmp,crlf())
	DELETE a_AccPick
	DIM a_AccPick[vListCt] as C
	a_AccPick.initialize(Acc_list_tmp)
	ok_button_label = "&OK"
	cancel_button_label = "&Cancel"
	varC_result = ui_dlg_box("test",<<%dlg%
	{region}
	Type prompt here:| [%M%.40,30AccPick^#a_AccPick];
	{endregion};
	{line=1,0};
	{region}
	<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
	{endregion};
	%dlg%)
 
'[COLOR="blue"] replace crlf() list with with commas so Word() function will work[/COLOR]	dim shared vaccLR as C
	dim shared vwrdnum as N
	dim shared vPickCt as N
	vPickCt = w_count(AccPick)
	vAccLR = stritran(AccPick,crlf(),",")

[COLOR="blue"]' cycle through list[/COLOR]
    for i = 1 to vPickCt
	vwrdnum = i
	dim vNowAcc as C
	vNowAcc = word(vaccLR,vwrdnum,",",1)

' [COLOR="GREEN"]========script to open table filtered to this account and install the variables goes here[/COLOR]
	tbl = table.open("[COLOR="Red"]YOUR TABLE[/COLOR]")
	query.filter = "[COLOR="red"]Account = "+s_quote(vNowAcc)[/COLOR]
	query.order=""
	query.options=""
	xi = tbl.query_create()
	
	tbl.fetch_first()
	while .NOT. tbl.fetch_eof()
		tbl.change_begin()
			'YOUR TABLE FIELDS AND RINC1-10 VALUES HERE
		tbl.change_end(.t.)
	tbl.fetch_next()
	end while
	xi.drop()
	tbl.close()
next
      The thing I haven't figured out is how to dim the multi-select Xdialog so it is not highlighted upon follow up. Searched the MB and docs and haven't found it.

      Hope this helps.

      Mike W
      Mike W
      __________________________
      "I rebel in at least small things to express to the world that I have not completely surrendered"

      Comment

        #4
        Re: Update Tables by %

        Andy,

        Correction:
        IF YOUR ACCOUNT FIELD IS NOT NUMERIC, I don't know how this will work.
        Should be if your account IS numeric, I don't know how this will work.

        Mike W
        Mike W
        __________________________
        "I rebel in at least small things to express to the world that I have not completely surrendered"

        Comment

          #5
          Re: Update Tables by %

          Hi Mike,

          Here is the solution I came up with, I have done it this way so that the program can run over night without any user intervention.

          can you foresee any problems with this method

          Code:
          'Date Created: 14-Feb-2006 08:40:38 AM
'Last Updated: 25-Nov-2006 04:50:45 PM
'Created By  : Andy Meer
'Updated By  : Andy Meer
'Insert label into script: 'Start'.
START:


dim rinc1 as n
dim rinc2 as n
dim rinc3 as n
dim rinc4 as n
dim rinc5 as n
dim rinc6 as n
dim rinc7 as n
dim rinc8 as n
dim rinc9 as n
dim rinc10 as n
rinc1 = 0
rinc2 = 0
rinc3 = 0
rinc4 = 0
rinc5 = 0
rinc6 = 0
rinc7 = 0
rinc8 = 0
rinc9 = 0
rinc10 = 0
isnotup = ""
service = ""
accountup = ""
dateup = date()
accountup = lookup("account_to_update","dateup =  date().and. updated = '' ","account")
if accountup = "" then
    end
end if 
service = lookup("account_to_update","account = '"+Var->accountup+"'.and. dateup = date().and. updated = ''","service")
rinc1 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone1")
rinc2 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone2")
rinc3 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone3")
rinc4 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone4")
rinc5 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone5")
rinc6 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone6")
rinc7 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone7")
rinc8 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone8")
rinc9 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone9")
rinc10 = lookup("account_to_update","account = '"+Var->accountup+"' .and. service = '"+Var->service+"' .and. dateup = date()","rzone10")


a_tbl = table.open("beratetest")

DIM a5_operation_order as C
a5_operati
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operati+Var->accountup+"' .and. service = '"+Var->service+"'" 
query.filter = a5_operation_filter
query.opti
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")

update.fields = 10
update.field1 = "RZONE1"
update.expr1 = "RZONE1*"+var->rinc1
update.field2 = "RZONE2"
update.expr2 = "RZONE2*"+var->rinc2
update.field3 = "RZONE3"
update.expr3 = "RZONE3*"+var->rinc3
update.field4 = "RZONE4"
update.expr4 = "RZONE4*"+var->rinc4
update.field5 = "RZONE5"
update.expr5 = "RZONE5*"+var->rinc5
update.field6 = "RZONE6"
update.expr6 = "RZONE6*"+var->rinc6
update.field7 = "RZONE7"
update.expr7 = "RZONE7*"+var->rinc7
update.field8 = "RZONE8"
update.expr8 = "RZONE8*"+var->rinc8
update.field9 = "RZONE9"
update.expr9 = "RZONE9*"+var->rinc9
update.field10 = "RZONE10"
update.expr10 = "RZONE10*"+var->rinc10



a_tbl.update()


GOTO CONTINUE13022006192837640
ERROR13022006192837640:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE13022006192837640:
a_tbl.close()

upacc = "y"
tbl = table.open("account_to_update")

query.order = "recno()"
'query.filter = ""
query.filter = "account = '"+Var->accountup+"' .and. service = '"+Var->service+"' "
query.opti
tbl.query_create()

update.fields = 1
update.field1 = "updated"
update.expr1 = "\"y\""

tbl.update()
tbl.query_detach_all()
tbl.close()
xbasic_wait_for_idle()





GOTO START
          thanks

          Andy

          Comment

            #6
            Re: Update Tables by %

            Andy,

            a_tbl = table.open("beratetest")

            DIM a5_operation_order as C
            a5_operati
            query.order = a5_operation_order
            DIM a5_operation_filter as C
            a5_operati+Var->accountup+"' .and. service = '"+Var->service+"'"
            query.filter = a5_operation_filter
            query.opti
            query.description = "Temporary Query"
            i_indx = a_tbl.query_create("N")

            Don't know what the green colored items are.
            I don't see a query filter defined.
            I thought there was a user selection process of accounts to be updated that I don't see here, and which I spent time establishing for you process for that as requested.

            Other than that, I didn't study it to such a degree that it fully played in my head to discover more questions.

            Mike W
            Mike W
            __________________________
            "I rebel in at least small things to express to the world that I have not completely surrendered"

            Comment

            Previous template Next
            Working...
            X