Get a Jumpstart with our Sample Apps
Results 1 to 14 of 14

Thread: how to update multiple selected records?

  1. #1
    Member
    Real Name
    Francis Dizon
    Join Date
    Jan 2006
    Location
    Philippines
    Posts
    404

    Default how to update multiple selected records?

    hi to all


    its been a while the last time i used alpha five. im kinda rusty right now.
    a client of mine came to a problem and i need to modify selected records on my employee table
    filtered with card_expiry in between these dates 1/26/2018 to 04/27/2018
    and id like to modify the Employee_ID 2nd and 3rd characters

    here is the sample thats in the records
    TCM-MC-000777
    and should be
    TMC-MC-000777

    i dont know how to create the update operation
    please help
    thanks
    Attached Files Attached Files
    Francis

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,642

    Default Re: how to update multiple selected records?

    ? strtran("TCM-MC-000777","TCM","TMC")
    = "TMC-MC-000777"

    So you could run an update operation on the Employee_ID field with the expression

    strtran(Employee_ID,"TCM","TMC")

    and if you wanted to be extra explicit filter for records where

    left(Employee_ID,3) = "TCM"
    Last edited by Stan Mathews; 06-01-2017 at 09:15 AM.
    There can be only one.

  3. #3
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,163

    Default Re: how to update multiple selected records?

    Is the Employee ID field used to link to other tables?
    Robin

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

  4. #4
    Member
    Real Name
    Francis Dizon
    Join Date
    Jan 2006
    Location
    Philippines
    Posts
    404

    Default Re: how to update multiple selected records?

    Thanks Stan

    i will try
    Francis

  5. #5
    Member
    Real Name
    Francis Dizon
    Join Date
    Jan 2006
    Location
    Philippines
    Posts
    404

    Default Re: how to update multiple selected records?

    MoGrace,

    it is linked only into a primary table for lookup. no transactions made yet
    no records will be orphaned
    Francis

  6. #6
    Member
    Real Name
    Francis Dizon
    Join Date
    Jan 2006
    Location
    Philippines
    Posts
    404

    Default Re: how to update multiple selected records?

    problem solved

    thanks Stan
    Francis

  7. #7
    Member
    Real Name
    Mike Galvin
    Join Date
    Aug 2005
    Location
    Florida
    Posts
    15

    Default Re: how to update multiple selected records?

    On the same lines of this thread.. I have created an Update Operation to delete part of the string in "Name" based off of the following:
    The Field Name is "Name" the Expression is : STRTRAN(NAME,"SN1550""") and the Filter Expression is : right(alltrim(name),len("SN1550")) = "SN1550". I would change this to other SN#'s to delete many strings at one time.
    Everything works fine...
    I am now down to a bunch of records that have part of the string that starts with "SN" and end with a bunch of different numbers; IE: 207,159,483 etc. etc. , but only have a few related matches...
    This is the last word in the string. Is there anyway, I can get the rest of these with one Expression ??? I have tried using ""SN***" with no results...

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,642

    Default Re: how to update multiple selected records?

    A bit confused but it seems you are looking for

    left(name,2) = "SN"
    There can be only one.

  9. #9
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,353

    Default Re: how to update multiple selected records?

    Quote Originally Posted by MikeG View Post
    On the same lines of this thread.. I have created an Update Operation to delete part of the string in "Name" based off of the following:
    The Field Name is "Name" the Expression is : STRTRAN(NAME,"SN1550""") and the Filter Expression is : right(alltrim(name),len("SN1550")) = "SN1550". I would change this to other SN#'s to delete many strings at one time.
    Everything works fine...
    I am now down to a bunch of records that have part of the string that starts with "SN" and end with a bunch of different numbers; IE: 207,159,483 etc. etc. , but only have a few related matches...
    This is the last word in the string. Is there anyway, I can get the rest of these with one Expression ??? I have tried using ""SN***" with no results...
    Mike

    Can you give a more complete example of the data?

    Instead of telling us about it in words, give a list of sample data with the complete value and how you want to select.

    Thanks.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    Member
    Real Name
    Mike Galvin
    Join Date
    Aug 2005
    Location
    Florida
    Posts
    15

    Default Re: how to update multiple selected records?

    Quote Originally Posted by Al Buchholz View Post
    Mike

    Can you give a more complete example of the data?

    Instead of telling us about it in words, give a list of sample data with the complete value and how you want to select.

    Thanks.
    Al,
    Here is a sample of the data that is listed in the Name Field : The SN# is always at the end but can vary in size, there could be from 1 to 4 digits after "SN"

    DeVante Parker*RR, SN156
    Melvin Gordon*RR, SN45
    Nelson Agholor*RR, SN178
    Breshad Perriman*RR, SN115
    Phillip Dorsett*RR, SN108
    T.J. Yeldon*RR, SN576
    Devin Smith*RR, SN121
    Dorial Green-Beckham*RR, SN87
    Devin Funchess*RR, SN126
    Ameer Abdullah*RR, SN39
    Jason Pierre-Paul*SN420
    Paul Posluszny*SN639
    Darrelle Revis*SN328
    Haloti Ngata*SN255

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,642

    Default Re: how to update multiple selected records?

    strtran(alltrim(name),right(alltrim(name),len(alltrim(name))- at("SN",name)+1),"")

    this will leave the ", " or "*" but those are easily removed later.
    Last edited by Stan Mathews; 11-12-2017 at 09:23 PM.
    There can be only one.

  12. #12
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,353

    Default Re: how to update multiple selected records?

    I went a little farther than Stan to show what is happening..

    Load this as an xbasic script and run it

    Code:
    namelist=<<%nl%
    DeVante Parker*RR, SN156
    Melvin Gordon*RR, SN45
    Nelson Agholor*RR, SN178
    Breshad Perriman*RR, SN115
    Phillip Dorsett*RR, SN108
    T.J. Yeldon*RR, SN576
    Devin Smith*RR, SN121
    Dorial Green-Beckham*RR, SN87
    Devin Funchess*RR, SN126
    Ameer Abdullah*RR, SN39
    Jason Pierre-Paul*SN420
    Paul Posluszny*SN639
    Darrelle Revis*SN328
    Haloti Ngata*SN255 
    %nl%
    msgg= ""
    for each name in namelist
    
    p1 = name+"~"
    p2 = alltrim(name)+"~"
    p3a = len(alltrim(name))
    p3b = at("SN",name)+1
    p3 = len(alltrim(name))- at("SN",name)+1
    p4 = right(alltrim(name),len(alltrim(name))- at("SN",name)+1)
    p5 = strtran(alltrim(name),right(alltrim(name),len(alltrim(name))- at("SN",name)+1),"")
    
    msgg= msgg+ \
    "p1: "+p1+crlf()+\
    "p2: "+p2+crlf()+\
    "p3a: "+p3a+crlf()+\
    "p3b: "+p3b+crlf()+\
    "p3: "+p3+crlf()+\
    "p4: "+p4+crlf()+\
    "p5: "+p5+crlf(2)
    
    next name
    
    a5_show_variable(msgg)
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  13. #13
    Member
    Real Name
    Mike Galvin
    Join Date
    Aug 2005
    Location
    Florida
    Posts
    15

    Default Re: how to update multiple selected records?

    Thanks Stan...
    This did the trick... Thank you very much ...
    MikeG

  14. #14
    Member
    Real Name
    Mike Galvin
    Join Date
    Aug 2005
    Location
    Florida
    Posts
    15

    Default Re: how to update multiple selected records?

    Thanks Al... I will try...
    MikeG

Similar Threads

  1. How to Update Selected Records with SQL, using the checkbox select column?
    By cpgood in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 06-06-2016, 11:45 PM
  2. Update multiple records at once
    By WebDesignShop in forum Mobile & Browser Applications
    Replies: 7
    Last Post: 10-17-2015, 07:40 PM
  3. Formating Multiple Values Selected
    By mstricky in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 04-24-2012, 06:39 AM
  4. Update multiple records from button
    By boatsurvey in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 03-25-2009, 06:09 AM
  5. Update - uses all recs not selected
    By dik_coleman in forum Alpha Five Version 6
    Replies: 2
    Last Post: 03-13-2005, 08:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •