Alpha DevCon 2018
Results 1 to 19 of 19

Thread: Functions not working after latest release installed

  1. #1
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Functions not working after latest release installed

    Now as regular contributors know, I'm no coder. A kind member sent me this function and it worked fine up until I loaded the latest version of V9.

    It calculates monthly complaint totals from a particular site.

    Can anyone help with this as I'm stumped.
    Tried going into Design and re saving, still doesn't work.

    FUNCTION past_year_sum AS C ( )

    ' site variable from site_select()
    ' This function sums the number of complaints for each month spanning the past 12 months including the the current month
    ' this function is called from the "display last 12 months" button on the F_comp form

    ' dim the needed variables
    dim vtitle as C
    vtitle = "Past 12 Month Summary" + crlf() + "====================" + crlf()
    dim vfooter as C
    vfooter = "====================" + crlf() + "Total = "
    dim vSum as C = "" ' accumulating result variable
    dim vcMWname as C ' month name
    dim vnCount as N = 0 ' montly count
    dim vntotal as N = 0 ' monthly count total

    dim vfS as C ' filter start date variable
    dim vfE as C ' filter end date variable
    dim vfilter as C ' filter string

    ' define the filter's first month start and end point variable values
    vfS = dtoc(month_end(date()) - 365)
    vfE = dtoc(month_end(ctod(vfS)))

    ' start with the first month acquiring that month's count, adding it to the accumulaing result variable (blank at start) and
    ' then incrementing the month for the next cycle

    for i = 1 to 12
    ' name on the first month in the series
    vcMWname = cmonth(ctod(vfS))

    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    ' get record counts for the month current in the script
    vfilter = between_date("Comp_Date",ctod(Var->vfS),ctod(Var->vfE)) + " .and. site_id = "+quote(vSID)
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    vncount = a5_get_records_in_query("comp",vfilter)

    ' add the month's count and month's name to the accumulating result variable
    vSum = vSum + vncount + space(2) + alltrim(vcMWname) + crlf()
    vntotal = vntotal + vnCount

    ' increment the month to the next month for the next cycle
    vfS = dtoc(addmonths(ctod(vfS),1))
    vfE = dtoc(month_end(ctod(vfS)))
    next i

    ' compile the tittle, accumulated variable, and footer into a single reporting results variable

    vSum = vtitle + alltrim(vSum) + crlf() + vfooter + vntotal

    ' display the result in the form text box
    parentform:text1.text = vsum

    END FUNCTION

  2. #2
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,381

    Default Re: Functions not working after latest release installed

    Ted,

    Need the F_comp form and some data to test this

  3. #3
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    Hi Lance, attached is what I have been working with. It is the original.

    I had hoped to recalculate the complaints when the record changed and I though I was there, but it stopped altogether.

    The upload is a bit iffy today as well!

    Ted

  4. #4
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,381

    Default Re: Functions not working after latest release installed

    Ok Ted, got it

    You need to change the code to this
    Code:
    FUNCTION past_year_sum AS C ( )
        
    ' site variable from site_select()
    '  This function sums the number of complaints for each month spanning the past 12 months including the the current month 
    '  this function is called from the "display last 12 months" button on the F_comp form
    
    ' dim the needed variables 
    dim vtitle as C
        vtitle = "Past 12 Month Summary" + crlf() + "====================" + crlf()
    dim vfooter as C
        vfooter = "====================" + crlf() + "Total = " 
    dim vSum as C = ""            ' accumulating result variable
    dim vcMWname as C            ' month name
    dim vnCount as N = 0        ' montly count
    dim vntotal as N = 0        ' monthly count total
    
    dim vfS as C                ' filter start date variable
    dim vfE as C                ' filter end date variable
    dim vfilter as C            ' filter string
    
    ' define the filter's first month start and end point variable values 
        vfS = dtoc(month_start(addyears(date(),-1)))
        vfE = dtoc(month_end(ctod(vfS)))
        
    ' start with the first month acquiring that month's count, adding it to the accumulaing result variable (blank at start) and
    ' then incrementing the month for the next cycle
    
        for i = 1 to 12
            ' name on the first month in the series
            vcMWname = cmonth(ctod(vfS))
    
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx        
            ' get record counts for the month current in the script
            vfilter = between_date("Comp_Date",ctod(Var->vfS),ctod(Var->vfE)) + " .and. site_id = "+quote(vSID)
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            
            
            vncount = a5_get_records_in_query("comp",vfilter)        
            
            ' add the month's count and month's name to the accumulating result variable
            vSum = vSum + vncount + space(2) + alltrim(vcMWname) + crlf()
            vntotal = vntotal + vnCount
        
            ' increment the month to the next month for the next cycle
            vfS = dtoc(addmonths(ctod(vfS),1))
            vfE = dtoc(month_end(ctod(vfS)))
        next i
    
    ' compile the tittle, accumulated variable, and footer into a single reporting results variable 
    
        vSum = vtitle + alltrim(vSum)  + crlf() +  vfooter  + vntotal
    
    ' display the result in the form text box
        parentform:text1.text = vsum
    
    END FUNCTION
    the problem was in the definition of the vfS for the filters date, it was taking 365 days off today's value and looking at the month end. with the above you will always get the first and last day of a month a year ago as the base date to start from. In your code you only need to change the line in red to make it work.

  5. #5
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    You are truly a star!
    I'd never have found that in a million years!

    Many thanks Lance.

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    Lance, I don't want to pester you, but where would I put the code to show only the unique sites? In the tables I am using I cannot have just a single occurrence, and it would be nice to show just the unique items. I had a look at the List function and ran away scared!

    Ted

  7. #7
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,381

    Default Re: Functions not working after latest release installed

    Not sure what you are trying to achieve Ted, do you want the browse on the F_comp form to only show 1 entry for each site instead of the 8 it shows now?

  8. #8
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,127

    Default Re: Functions not working after latest release installed

    Ted,
    I am with Lance. I am not certain what your goal is with your last post. Describe your endpoint goal and I am sure we can help.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  9. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    Thanks guys,

    The application I have developed has complaints logged against a site.
    In the example you have seen, there is a master table which contains unique occurrences of the Site.

    I cannot do it that way (well I could but it would screw up a whole lot of other functionality)

    Plese see attached. I'm trying to display unique site names.
    You will see "1&2 The Hedger" etc appears twice, and presumably as many times as it has a complaint against it.
    I'd like to display it only once so that the selection is easier.

    Regards, Ted

  10. #10
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,005

    Default Re: Functions not working after latest release installed

    Try adding a line to the site_select() function.


    Code:
    'Create an XDialog dialog box to prompt for parameters.
    DIM shared vSID as C
    DIM SHARED vresult as C
    vSID = "kl=site,{keylist_build(\"H=.05,1:10[Site_Id],2:16[Site_Name]\",''+Site_id,left(''+Site_id,10),left(''+Site_name,16))}{}"
    vSID = *remove_duplicates(vSID,crlf())

  11. #11
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,784

    Default Re: Functions not working after latest release installed

    ...or. you can also use a filter in the 'keylist' line.
    Code:
    vSID = "kl=site,{keylist_build(\"H=.05,1:10[Site_Id],2:16[Site_Name]\",''+Site_id,left(''+Site_id,10),left(''+Site_name,16))}{.t. .and. unique_key_value()}"
    When setting up the record-list list box in the genie you can specify a filter. In the filter builder go to the last tab, 'Query options' and check the box for Unique records only.
    Tim Kiebert
    Geelong Citrus Packers
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  12. #12
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,127

    Default Re: Functions not working after latest release installed

    Tim,
    I have found the unique_key_value() within a filter expresssion for key list build and table.external_record_content_get() to be completely unreliable. I typically second line it with word_unique() or *remove_duplicates() as Stan suggested.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  13. #13
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,784

    Default Re: Functions not working after latest release installed

    Hi Mike,

    The trick with unique_key_value() is that the records need to be sorted (manually, by xbasic, or using an index) on the same key that you want to be unique. The field specified as the sort becomes the key that is tested for uniqueness.

    In the case of record-list boxes the sort is performed on the first column. So if the first column in your list box was say the record id (which should always be unique) then you would still get all the records. Even though you may actually be interested in the second column.

    For table.external_record_content_get() you can specify the sort order.

    Using the 'Unique records only' in the query genie you need to specify the field/s to sort on for it to have any affect. (for example if using a default browse.)

    I am not saying that unique_key_value() is the the/my preferred way, just that it is a possible way. (I got it to work fine in the sample provided in this thread.) I have used the methods you mentioned as well when unique_key_value() was not suitable.
    Tim Kiebert
    Geelong Citrus Packers
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  14. #14
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,381

    Default Re: Functions not working after latest release installed

    Just a note for Ted, and further to what has been said by Stan, Mike and Tim, the *remove_duplicates() needs to be sorted ascending first as well.

  15. #15
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,127

    Default Re: Functions not working after latest release installed

    Quote Originally Posted by Lance Gurd View Post
    Just a note for Ted, and further to what has been said by Stan, Mike and Tim, the *remove_duplicates() needs to be sorted ascending first as well.
    For this reason, I tend to use word_unique()

    Code:
    dim L1 as C
    L1 = <<%a%
    cats
    dogs
    hogs
    cows
    horses
    cows
    cats
    %a%
    
    ? *remove_duplicates(L1,crlf())
    = cats
    dogs
    hogs
    cows
    horses
    cows
    cats
    
    
    ? word_unique(L1,crlf())
    = cats
    dogs
    hogs
    cows
    horses
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  16. #16
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,005

    Default Re: Functions not working after latest release installed

    Word_unique() does seem to be the best choice. Just for anyone else who is following the thread you can...

    Code:
    dim L1 as C
    L1 = <<%a%
    cats
    dogs
    hogs
    cows
    horses
    cows
    cats
    %a%
    
    
    ? *remove_duplicates(sortsubstr(L1,crlf()))
    = cats
    cows
    dogs
    hogs
    horses

  17. #17
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    Thank you all very much indeed. I will grab the solutions and plug 'em in!
    Please excuse the delay in replying.
    As well as developing apps, I'm now Project Managing a huge back Scanning operation into EDRMS (sob! real tears!). 1000 miles a week + travelling and eating junk food.

  18. #18
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Functions not working after latest release installed

    Stan, tried and makes no difference - still getting all of the records, including dupes.
    Ted

  19. #19
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,005

    Default Re: Functions not working after latest release installed

    Doesn't happen with your original sample. If it does with your live data you'll have to provide a sample illustrating the problem.

Similar Threads

  1. netmailer not installed?
    By Ian_Edwards in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 01-04-2009, 07:17 PM
  2. Printers Not Installed
    By graysarge in forum Web Application Server v6
    Replies: 2
    Last Post: 03-26-2005, 01:00 PM
  3. V6 Installed- Keep V5 ?
    By Ken Davis in forum Alpha Five Version 6
    Replies: 2
    Last Post: 02-06-2005, 05:37 PM
  4. Field Rules not working for sets in 2012 release
    By vwater in forum Alpha Five Version 6
    Replies: 14
    Last Post: 09-23-2004, 08:42 AM
  5. New scanner installed
    By Lee Garrett in forum Alpha Five Versions 2 and 3
    Replies: 0
    Last Post: 04-26-2001, 09:05 PM

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
  •