Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 35

Thread: Inserting data

  1. #1
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Inserting data

    Hi there, I having trying many ways to insert new data into my customer table. The customerid was the key and it uses the First character of the Last name follow by running number. How do I append new customer to the next record. Below is the before and after results. Please highlight me how to do it.

    Thanks

    Before.jpg

    After.jpg

  2. #2
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    It's extremely helpful to see the code you are using.
    It would also be helpful to know if you are using dbfs or sql, and if sql, which "flavor".

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

    Default Re: Inserting data

    You can add a Quick Sort to the onsave event of the browse to resort the data in order of the Customer ID.

    Use Action Scripting to help generate the code needed.

    So the data will appear in the order you requested after a save of the data.

    The underlying data in the table is still in chronological order, but is displayed in Customer ID order.

    Not sure how you are generating the Customer ID, so the timing can be an issue.

    (and what Gregg asked for is helpful too..)
    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.

  4. #4
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Al,
    From the looks of the images supplied, each letter of the alphabet has it's own running total (ie: there are potentially 26 customer ids ending with 0001).
    This requires more than just a simple sort (at least in my opinion).

    Leong : On a practical side, I won't tell you this is wrong, but I will ask you what happens when a female client changes her last name
    due to marriage or divorce ?

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

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    Al,
    From the looks of the images supplied, each letter of the alphabet has it's own running total (ie: there are potentially 26 customer ids ending with 0001).
    This requires more than just a simple sort (at least in my opinion).
    No given the data shown, a simple sort works. It does have a limit of 99,999 per prefix letter.
    Remember Y2K? Back in the 1960's no one thought by 2000 we'd still be using the data collected earlier. YEAH!

    What is interesting to me is how the ID is generated. Haven't heard back.
    I know how to do it.
    Have a control table that has entries for all prefixes need and a counter field to increment.
    Just curious how this one works.
    Quote Originally Posted by madtowng View Post
    Leong : On a practical side, I won't tell you this is wrong, but I will ask you what happens when a female client changes her last name
    due to marriage or divorce ?
    Yes the always changing names usually suggests an actual ID field that doesn't change and a reference field that does change as data changes. (ie the ID field shown)
    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.

  6. #6
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    For my thinking, this is far easier to do with sql than dbfs, even though it is very doable with either.

  7. #7
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    It's extremely helpful to see the code you are using.
    It would also be helpful to know if you are using dbfs or sql, and if sql, which "flavor".
    I have no idea how to start, the table is link to MSSQL database. The biggest issue was how to get the last record of that particular character of the Last Name.

    Cheers

  8. #8
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    Al,
    From the looks of the images supplied, each letter of the alphabet has it's own running total (ie: there are potentially 26 customer ids ending with 0001).
    This requires more than just a simple sort (at least in my opinion).

    Leong : On a practical side, I won't tell you this is wrong, but I will ask you what happens when a female client changes her last name
    due to marriage or divorce ?
    Hi Gregg, the Customer ID will not change even a female client changes her last name.

  9. #9
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    This code will easily get the highest customerID.
    More help can be provided if we see what you have going.

    Code:
    select max(customerID) from customerTable
    where left(customerID,1)=left(lastName,1)

  10. #10
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by Al Buchholz View Post
    No given the data shown, a simple sort works. It does have a limit of 99,999 per prefix letter.
    Remember Y2K? Back in the 1960's no one thought by 2000 we'd still be using the data collected earlier. YEAH!

    What is interesting to me is how the ID is generated. Haven't heard back.
    I know how to do it.
    Have a control table that has entries for all prefixes need and a counter field to increment.
    Just curious how this one works.


    Yes the always changing names usually suggests an actual ID field that doesn't change and a reference field that does change as data changes. (ie the ID field shown)
    Hi Al, I have thought of using a control table with all the 26 Alphabets and the last running number. This was from an old VB6 program. Upon saving the data, the program will look at the existing table and generate the new customer ID.

  11. #11
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Leong,

    I am stabbing at the solution here because I have no real sample data to work with.
    The code below "should" work, but obviously needs to be tested with actual tables and columns.
    Code:
    set customerID = concat(left(m.lastName,1),right((select max(q.customerID) from customerTable q where left(q.lastName,1)=left(m.lastName,1)),4)+1)

  12. #12
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Looks like I missed a major part.
    The code should be
    Code:
    set m.customerID = concat(left(m.lastName,1),format(right((select max(q.customerID) from customerTable q where left(q.lastName,1)=left(m.lastName,1)),5)+1,'0000#'))

  13. #13
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    Looks like I missed a major part.
    The code should be
    Code:
    set m.customerID = concat(left(m.lastName,1),format(right((select max(q.customerID) from customerTable q where left(q.lastName,1)=left(m.lastName,1)),5)+1,'0000#'))
    Hi Gregg, this seem like SQL script?

  14. #14
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    It is a sql script. If you were using MySQL I would say put it in a before Insert trigger, but mssql doesn't have those.
    It could fit nicely in an after insert trigger.

  15. #15
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Sometimes something kicks in after I submit a response.
    This code could work if you really want to do it with xbasic.
    To me, it makes more sense than keeping 26 separate tables or maintaining
    26 different state variables.
    Code:
    dim ciCN as sql::connection
    dim ciArgs as sql::arguments
    dim idnum as c
    ciArgs.set("cid",left(lastname,1))
    ciCN.open(<sql connection>)
    idnum = sql_lookup(ciCN,"<customerTable>","left(max(customerID),1)=:cid","format(right(max(customerID),5)+1),'0000#')",ciArgs)
    customerID = left(lastName,1)+idnum

  16. #16
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Sorry Gregg, didn't manage to get how your code work. But I tried the following.

    dim shared vFamid as C
    dim vFIDLast as C
    dim vCFID as C

    vFamid = "C00001"
    vFIDLast = tablemax("biocustomer","customerid = "+s_quote(vfamid),"customerid")
    vCFID = increment_value(vFIDlast)


    The result I got was vCFID="C00002" instead of "C00004", where did I go wrong?


    Before.jpg
    After.jpg

  17. #17
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Nice job finding the increment_value() function.
    Looks like you changed to dbfs from sql.
    Try this code.
    Code:
    vFamid = "C"
    vFIDLast = tablemax("biocustomer","left(customerid,1) = "+s_quote(vfamid),"customerid") 
    vCFID = increment_value(vFIDlast)

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

    Default Re: Inserting data

    While you can use the customer table/index method, it does have holes in it.

    1. It relies on an index based on the customer table. If the index become corrupted, multiple issues can happen - ie wrong values assigned, orphaned child records.

    2. Having used this method and the control table method, I still use the control table. A little more work, but much less likely to fail.

    3. No method is perfect. At least not in this life. Some are more solid than others. Choose wisely.
    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.

  19. #19
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    Nice job finding the increment_value() function.
    Looks like you changed to dbfs from sql.
    Try this code.
    Code:
    vFamid = "C"
    vFIDLast = tablemax("biocustomer","left(customerid,1) = "+s_quote(vfamid),"customerid") 
    vCFID = increment_value(vFIDlast)
    Thanks Gregg, it work perfectly but now it get a bit complex. I noticed there are same Customer ID but with different Branch code. If the customer is from Branch code "AMK", the next ID should be "C00003" but it display "C00004" instead. How can I add a filter that only select the last record from certain Branch code?


    Before01.jpg
    Last edited by samt2046; 10-02-2019 at 02:08 AM.

  20. #20
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by Al Buchholz View Post
    While you can use the customer table/index method, it does have holes in it.

    1. It relies on an index based on the customer table. If the index become corrupted, multiple issues can happen - ie wrong values assigned, orphaned child records.

    2. Having used this method and the control table method, I still use the control table. A little more work, but much less likely to fail.

    3. No method is perfect. At least not in this life. Some are more solid than others. Choose wisely.

    Thanks Alan, this will be my last resort if I cannot find other solutions.

  21. #21
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    ok...try this
    Code:
    vFamid = "C"
    vBranch = "AMK"
    vFIDLast = tablemax("biocustomer","branch=vBranch .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid") 
    vCFID = increment_value(vFIDlast)

  22. #22
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by madtowng View Post
    ok...try this
    Code:
    vFamid = "C"
    vBranch = "AMK"
    vFIDLast = tablemax("biocustomer","branch=vBranch .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid") 
    vCFID = increment_value(vFIDlast)
    I have tried but got the error "Variable type mismatch: Cannot assign data of Type"

  23. #23
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,875

    Default Re: Inserting data

    vFamid = "C"
    vBranch = "AMK"
    vFIDLast = tablemax("biocustomer","branch=vBranch .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid")
    vCFID = increment_value(vFIDlast)
    It probably cannot resolve vBranch,

    try

    vFIDLast = tablemax("biocustomer","branch=s_quote(vBranch) .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid")

    Tom

  24. #24
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Quote Originally Posted by Tom Henkel View Post
    It probably cannot resolve vBranch,

    try

    vFIDLast = tablemax("biocustomer","branch=s_quote(vBranch) .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid")

    Tom
    Hi Tom, still having the same error, I wonder where went wrong, I even tried.

    vFIDLast = tablemax("biocustomer","branch=val->vBranch .and. left(customerid,1) = "+s_quote(vfamid) ,"customerid")

    Cheers

  25. #25
    Member
    Real Name
    Jon P Moody
    Join Date
    Nov 2016
    Posts
    179

    Default Re: Inserting data

    shouldn't this be
    Code:
    vFIDLast = tablemax("biocustomer","branch = " + s_quote(vBranch) + " .and. left(customerid,1) = " + s_quote(vfamid) ,"customerid")

  26. #26
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,875

    Default Re: Inserting data

    My bad...


    code should be:
    "branch="+s_quote(vBranch)+" .and.

    because I had the "S_quote(vBranch)" within the quotes, the filter interpreted it as text, not a function.

    sorry

  27. #27
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    Part of the reason I stopped using dbfs is because the filters aren't as intuitive as with sql.
    Now that I realize this, I'm going to offer my preferred solution.

    This allows me to easily see what the filter looks like ie: branch = "AMK" .and. left(customerid,1) = "C"
    luFilter does not need to be a pointer variable, but I often use several different lookups (or sql queries) and
    this allows me to alter the original in a loop, yet not have to redefine from scratch each time.

    Finally, if it doesn't work, please post the actual code you are using.
    Ideally , provide some sanitized data for each table (in text so we can use it to create the tables).


    Code:
    dim vFamid as c
    dim vBranch as c
    dim luFilter[0] as p
    luFilter[].original = <<%txt%
    branch = "<vBranch>" .and. left(customerid,1) = "<vfamid>"
    %txt%
    
    vFamid = "C"
    vBranch = "AMK"
    luFilter[1].altered = strtran(luFilter[1].original,"<vBranch>",vBranch)
    luFilter[1].altered = strtran(luFilter[1].altered,"<vfamid>",vFamid)
    debug(1)
    vFIDLast = tablemax("biocustomer",luFilter[1].altered ,"customerid") 
    vCFID = increment_value(vFIDlast)

  28. #28
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    A big thanks to Tom and Gregg, both solutions works perfectly. Thanks again for all the help from all of you.

    Cheers

  29. #29
    Member
    Real Name
    Leong Tuck Shing
    Join Date
    Jun 2006
    Posts
    49

    Default Re: Inserting data

    Hi guys, I hit another brick wall.....I didn't realize the database was created in such way. I have just found out that there are extra character for some customer in the customer ID. From the image, if I want to find out the last ID for "G", it will go to the last record of "GE" instead. Prefix for GE is reserved for Guest only. I have try Fetch_Prev() but it only return the number "1031". Following is my code.

    vFamID = left(dbxLname,1)
    msgbox("Customer_ID",vfamid)

    if substr(vFamID,1,1) = "G"
    vFamID = "GE"
    dim luFilter[0] as p
    luFilter[].original = <<%txt%
    branchcode = "<vBranch>" .and. left(customerid,2) = "<vfamid>" %txt%
    luFilter[1].altered = strtran(luFilter[1].original,"<vBranch>",vBranch)
    luFilter[1].altered = strtran(luFilter[1].altered,"<vfamid>",vFamid)
    vFIDFirst = tablemin("biocustomer",luFilter[1].altered ,"customerid")
    msgbox("First FID",vFIDFirst)

    dim tbl as P
    tbl = table.current()
    tbl.index_primary_put("customerid")
    tbl.fetch_find("vfidfirst")
    vvfamid = tbl.fetch_prev()
    'msgbox("Previous",vvFamid)
    vCFID = increment_value(vvFamid)
    'msgbox("Next FID", vCFID)
    goto CONT1
    end if



    Extra.jpg

  30. #30
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Inserting data

    You have a lot more going on than you did before.
    I commented out several lines with ''' to test the code.
    This is quite different than what we had as you are now using tablemin() instead of tablemax() (is this a separate module?).
    The real changes I made (they should even work with the original code) should even work with the previous solution.

    Code:
    '''vFamID = left(dbxLname,1)
    '''msgbox("Customer_ID",vfamid)
    debug(1)
    vFamID = "G"
    'I added the vBranch to make the code work
    vBranch = "AMK"
    dim luFilter[0] as p
    ' My changes begin here
    luFilter[].original = <<%txt%
    branchcode = "<vBranch>" .and. left(customerid,2) = "<vfamid>" .and. left(customerid,2) <> "GE" 
    %txt%
    luFilter[].original = <<%txt%
    branchcode = "<vBranch>" .and. left(customerid,2) = "GE"  
    %txt%
    select
    	case left(vFamID,2) = "GE"
    		luFilter[2].altered = strtran(luFilter[2].original,"<vBranch>",vBranch)
    		luFilter[2].altered = strtran(luFilter[2].altered,"<vfamid>",vFamid)			 
    	case else
    		luFilter[1].altered = strtran(luFilter[1].original,"<vBranch>",vBranch)
    		luFilter[1].altered = strtran(luFilter[1].altered,"<vfamid>",vFamid)		
    end select
    'my changes end here
    vFIDFirst = tablemin("biocustomer",luFilter[1].altered ,"customerid") 
    msgbox("First FID",vFIDFirst)
    
    dim tbl as P
    tbl = table.current()
    tbl.index_primary_put("customerid")
    tbl.fetch_find("vfidfirst")
    vvfamid = tbl.fetch_prev()
    'msgbox("Previous",vvFamid)
    vCFID = increment_value(vvFamid)
    'msgbox("Next FID", vCFID)
    '''goto CONT1

Similar Threads

  1. inserting data into multiple tables(data binding)
    By amitloh in forum Mobile & Browser Applications
    Replies: 5
    Last Post: 07-03-2017, 02:19 PM
  2. Inserting data into embedded browse
    By Jcian in forum Alpha Five Version 9 - Desktop Applications
    Replies: 7
    Last Post: 06-15-2009, 03:03 PM
  3. Replies: 2
    Last Post: 06-05-2006, 07:58 AM
  4. Inserting data from one form into multiple tables
    By Page Sands in forum Alpha Five Version 6
    Replies: 15
    Last Post: 12-22-2004, 10:59 AM
  5. Lookups-Inserting data onto a form
    By Manish Bhalla in forum Alpha Five Version 4
    Replies: 2
    Last Post: 10-14-2000, 06:33 AM

Bookmarks

Posting Permissions

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