Alpha Video Training
Results 1 to 26 of 26

Thread: Can anyone help me with this expression?

  1. #1
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Can anyone help me with this expression?

    Attached is a description of what I would like to do.
    I am not good at math.

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Attached is a description of what I would like to do.
    I am not good at math.
    Where are you trying to do this? On a report, on a form? What is the field name of the field containing "# in Class"?

    Given a "# in class" (num) the expression for first place points is

    Code:
    case(between(num,3,4),1,num=5,2,num=6,3,num=7,4,between(num,8,15),5,between(num,16,23),6,between(num,24,31),7,between(num,32,39),8,num>39,9)
    for second place points

    Code:
    case(between(num,3,4),0,num=5,1,num=6,2,num=7,3,between(num,8,15),4,between(num,16,23),5,between(num,24,31),6,between(num,32,39),7,num>39,8)
    and you see the pattern for the rest.
    Last edited by Stan Mathews; 01-31-2007 at 10:32 AM.

  3. #3
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Trying to use it on a form. I want to total the number of entries in a class.
    In class 1 there are 3 animals entered. So if there are 3 entries, the first place wnner would be awarded 1 point to be posted in the field ALSA Points. and so no according to formula in the doc. Attached in my first post.

    In Class Column labled Class the field name is Classid
    In the column labled Placed the field name is Placed
    In the column labled ALSA Points the field name is ALSA_points

    Screen shot of form attached.

    I am new to Alpha5 v8, appreciate your help.

  4. #4
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Trying to use it on a form. I want to total the number of entries in a class.
    In class 1 there are 3 animals entered. So if there are 3 entries, the first place wnner would be awarded 1 point to be posted in the field ALSA Points. and so no according to formula in the doc. Attached in my first post.

    In Class Column labled Class the field name is Classid
    In the column labled Placed the field name is Placed
    In the column labled ALSA Points the field name is ALSA_points

    Screen shot of form attached in thread

    I am new to Alpha5 v8, appreciate your help.
    Last edited by jdoyle; 01-31-2007 at 01:02 PM. Reason: posted twice

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Trying to use it on a form. I want to total the number of entries in a class.
    In class 1 there are 3 animals entered. So if there are 3 entries, the first place wnner would be awarded 1 point to be posted in the field ALSA Points. and so no according to formula in the doc. Attached in my first post.

    In Class Column labled Class the field name is Classid
    In the column labled Placed the field name is Placed
    In the column labled ALSA Points the field name is ALSA_points

    Screen shot of form attached in thread

    I am new to Alpha5 v8, appreciate your help.
    The expression you want is

    Code:
    case(between(tablecount("llama_data","class = "+class),3,4).and.placed = 1,1,tablecount("llama_data","class = "+class)=5,case(placed=1,2,placed=2,1),tablecount("llama_data","class = "+class)=6,case(placed=1,3,placed=2,2,placed =3,1),tablecount("llama_data","class = "+class)=7,case(placed=1,4,placed=2,3,placed =3,2,placed=4,1),between(tablecount("llama_data","class = "+class),8,15),case(placed=1,5,placed=2,4,placed =3,3,placed=4,2,placed=5,1),between(tablecount("llama_data","class = "+class),16,23),case(placed=1,6,placed=2,5,placed =3,4,placed=4,3,placed=5,2,placed=6,1),between(tablecount("llama_data","class = "+class),24,31),case(placed=1,7,placed=2,6,placed =3,5,placed=4,4,placed=5,3,placed=6,2,placed=7,1),between(tablecount("llama_data","class = "+class),32,39),case(placed=1,8,placed=2,7,placed =3,6,placed=4,5,placed=5,4,placed=6,3,placed=7,2,placed=8,1),tablecount("llama_data","class = "+class)>39,case(placed=1,9,placed=2,8,placed =3,7,placed=4,6,placed=5,5,placed=6,4,placed=7,3,placed=8,2,placed=9,1))
    Last edited by Stan Mathews; 01-31-2007 at 01:24 PM.

  6. #6
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,200

    Default Re: Can anyone help me with this expression?

    Holly Cow, Stan!

    Jim posted his reply at 11:30 and you had that Leviathan code complete by by 12:18! Amazing! (I threw in that fancy word that I got from Peter Greulich to help me find this thread in the future).
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by Mike Wilson View Post
    Holly Cow, Stan!

    Jim posted his reply at 11:30 and you had that Leviathan code complete by by 12:18! Amazing! (I threw in that fancy word that I got from Peter Greulich to help me find this thread in the future).
    Sorry to be so slow. I am at work and somewhat distracted.

    Perhaps it won't look so imtimidating if you break it down in a word processor and see the repetitive nature of the solution. Most was just cut, pasted, and slightly altered.

    Code:
    case(
    between(tablecount("llama_data","class = "+class),3,4).and.placed = 1,1,
    tablecount("llama_data","class = "+class)=5,case(placed=1,2,placed=2,1),
    tablecount("llama_data","class = "+class)=6,case(placed=1,3,placed=2,2,placed =3,1),
    tablecount("llama_data","class = "+class)=7,case(placed=1,4,placed=2,3,placed =3,2,placed=4,1),
    between(tablecount("llama_data","class = "+class),8,15),case(placed=1,5,placed=2,4,placed =3,3,placed=4,2,placed=5,1),
    between(tablecount("llama_data","class = "+class),16,23),case(placed=1,6,placed=2,5,placed =3,4,placed=4,3,placed=5,2,placed=6,1),
    between(tablecount("llama_data","class = "+class),24,31),case(placed=1,7,placed=2,6,placed =3,5,placed=4,4,placed=5,3,placed=6,2,placed=7,1),
    between(tablecount("llama_data","class = "+class),32,39),case(placed=1,8,placed=2,7,placed =3,6,placed=4,5,placed=5,4,placed=6,3,placed=7,2,placed=8,1),
    tablecount("llama_data","class = "+class)>39,case(placed=1,9,placed=2,8,placed =3,7,placed=4,6,placed=5,5,placed=6,4,placed=7,3,placed=8,2,placed=9,1))
    Last edited by Stan Mathews; 01-31-2007 at 02:37 PM. Reason: attachment displays structure more clearly

  8. #8
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Tried the expression but get an "invalid or incomplete expression" error

    I have attached data base zip if that might help.

    Again thanks for your help.
    You stated that your are work. This is not an immediate emergency.

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Tried the expression but get an "invalid or incomplete expression" error

    I have attached data base zip if that might help.

    Again thanks for your help.
    You stated that your are work. This is not an immediate emergency.
    Placed is a character field in your table, I assumed numeric. Added quotes around all the placed = result tests.

    Code:
    case(between(tablecount("llama_data","class = "+class),3,4).and.placed = "1",1,tablecount("llama_data","class = "+class)=5,case(placed="1",2,placed="2",1),tablecount("llama_data","class = "+class)=6,case(placed="1",3,placed="2",2,placed ="3",1),tablecount("llama_data","class = "+class)=7,case(placed="1",4,placed="2",3,placed ="3",2,placed="4",1),between(tablecount("llama_data","class = "+class),8,15),case(placed="1",5,placed="2",4,placed ="3",3,placed="4",2,placed="5",1),between(tablecount("llama_data","class = "+class),16,23),case(placed="1",6,placed="2",5,placed ="3",4,placed="4",3,placed="5",2,placed="6",1),between(tablecount("llama_data","class = "+class),24,31),case(placed="1",7,placed="2",6,placed ="3",5,placed="4",4,placed="5",3,placed="6",2,placed="7",1),between(tablecount("llama_data","class = "+class),32,39),case(placed="1",8,placed="2",7,placed ="3",6,placed="4",5,placed="5",4,placed="6",3,placed="7",2,placed="8",1),tablecount("llama_data","class = "+class)>39,case(placed="1",9,placed="2",8,placed ="3",7,placed="4",6,placed="5",5,placed="6",4,placed="7",3,placed="8",2,placed="9",1))

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

    Default Re: Can anyone help me with this expression?

    Alternative expression using character based "placed" field.

    Code:
    case(between(tablecount("llama_data","class = "+class),3,4).and.placed = "1",max(val(placed),0),tablecount("llama_data","class = "+class)=5,max(3-val(placed),0),tablecount("llama_data","class = "+class)=6,max(4-val(placed),0),tablecount("llama_data","class = "+class)=7,max(5-val(placed),0),between(tablecount("llama_data","class = "+class),8,15),max(6-val(placed),0),between(tablecount("llama_data","class = "+class),16,23),max(7-val(placed),0),between(tablecount("llama_data","class = "+class),24,31),max(8-val(placed),0),between(tablecount("llama_data","class = "+class),32,39),max(9-val(placed),0),tablecount("llama_data","class = "+class)>39,max(10-val(placed),0))
    After creating an index for the table on the class field, expression class, this expression evaluates significantly faster and the calc field updates more smoothly.

    Code:
    case(between(dbcount("llama_data","class",class),3,4).and.placed = "1",max(val(placed),0),dbcount("llama_data","class",class)=5,max(3-val(placed),0),dbcount("llama_data","class",class)=6,max(4-val(placed),0),dbcount("llama_data","class",class)=7,max(5-val(placed),0),between(dbcount("llama_data","class",class),8,15),max(6-val(placed),0),between(dbcount("llama_data","class",class),16,23),max(7-val(placed),0),between(dbcount("llama_data","class",class),24,31),max(8-val(placed),0),between(dbcount("llama_data","class",class),32,39),max(9-val(placed),0),dbcount("llama_data","class",class)>39,max(10-val(placed),0))
    Last edited by Stan Mathews; 01-31-2007 at 05:42 PM. Reason: another idea

  11. #11
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Stan,
    Sorry for the trouble, but I am still having problems in making the expressions work. I have tried all of them and keep getting error of "Invalid or incomplete expression. I am not sure about the syntax of the fields. In your expression you have "llama_data" and the label in my data base is "llama data", you have "class" and I have "classid". I have changed the syntax to mach my labels and still does now work. Have attached screen shots and current ziped data base.

    Thanks
    Jim

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Stan,
    Sorry for the trouble, but I am still having problems in making the expressions work. I have tried all of them and keep getting error of "Invalid or incomplete expression. I am not sure about the syntax of the fields. In your expression you have "llama_data" and the label in my data base is "llama data", you have "class" and I have "classid". I have changed the syntax to mach my labels and still does now work. Have attached screen shots and current ziped data base.

    Thanks
    Jim

    I'll take a look in the morning.

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by Stan Mathews View Post
    I'll take a look in the morning.
    Looks like I lost track of this post, sorry. I'm making a note to look at it in the morning, really.

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by Stan Mathews View Post
    Looks like I lost track of this post, sorry. I'm making a note to look at it in the morning, really.
    I assumed from your screenshot that Placed was a character field (it was aligned left) so the places where I had val(placed) were causing the error on your end. ( Val(anynumber) is invalid. )

    Should be

    Code:
    case(between(tablecount("llama data","Classid = "+Classid),3,4).and.placed = 1,max(placed,0),tablecount("llama data","Classid = "+Classid)=5,max(3-placed,0),tablecount("llama data","Classid = "+Classid)=6,max(4-placed,0),tablecount("llama data","Classid = "+Classid)=7,max(5-placed,0),between(tablecount("llama data","Classid = "+Classid),8,15),max(6-placed,0),between(tablecount("llama data","Classid = "+Classid),16,23),max(7-placed,0),between(tablecount("llama data","Classid = "+Classid),24,31),max(8-placed,0),between(tablecount("llama data","Classid = "+Classid),32,39),max(9-placed,0),tablecount("llama data","Classid = "+Classid)>39,max(10-placed,0))
    and/or a similar editing of the dbcount version.

    I am working in A5V5 so feel free to ignore the following if it doesn't pertain to your version.

    One should never have punctuation in a table or field name, other than an underscore. You have a # and an &. I also avoid spaces in table names.

    While it is possible to have long table and field names, their use can lead to problems. There is a limit to the number of characters you can have in an expression like the above (something like 1024 in A5V5). If such an expression needed to be written for the "llama_class_shownumber" table, one could quickly run over the limit.

  15. #15
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Stan,
    Thanks, the code worked, but it does not store the calulated points.
    When you enter how the animal placed and go to the next field (ALSA_points) it shows the points but will not save the points in that field.
    Any suggestions?
    Jim

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Stan,
    Thanks, the code worked, but it does not store the calulated points.
    When you enter how the animal placed and go to the next field (ALSA_points) it shows the points but will not save the points in that field.
    Any suggestions?
    Jim
    To save that value you could have a calculated field in the table, not in the browse or on the form. The same expression would suffice.

    The problem with this approach is that the value is only recalculated when something in the same record changes which affects the calculation (patently impossible). As each new record is added you would need to do something to recalculate the field in all records. (Not sure if that is available in A4). You could re-evaluate the field rules from the control panel, but that is clumsy.

    I would suggest you make this a batch process. When you need the points value stored/recalculated for the records in the table, run an update operation on the field with the (now working) expression.

    But do you really need it stored?

  17. #17
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Stan,
    Thanks for your help.
    I am new to ALPHA5 and your instructions has taught me alot. It would have taken for ever to learn. The expression works fine and I got it to store in another *.dbf. with one little problem. After in put in form "Show Ring Entry" I get an error "Error posting to table llama data". However you go look at the llama data and the new information is there. Any suggestions?

    I don't know who this message board is sponsored by, but it is a great help.

    Thanks agin
    Jim

  18. #18
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Stan,
    You were kind enough to help me with this expression. It works ok, but I have run in to a problem. Example: There could be a class with 17 llamas shown in the class. The association rules that the judge must place 6 out of the class of 17. When you enter the placeings 1 thru 6 it calculates the points correctly. The other llamas shown get no points. So if the "placed" field for the other llamas is left blank it awards 6 points. These llamas that don't place are dismissed from the show ring and are not placed. However all llamas not placeing beyond 6th are given 6 points in the ALSA field. My question is if the other 9 llamas did not place how do I get the ALSA points to be 0 or blank.

    I have attached my data base as crude as it is if you care to look at it. You will find the examples under Show ring entry, also attached the place.doc for award points.

    I also think that I have post this question in the wrong section. I am using Alpha5 v8 Pro

    Thanks
    Jim

    If you can help if would be appreciated

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Stan,
    You were kind enough to help me with this expression. It works ok, but I have run in to a problem. Example: There could be a class with 17 llamas shown in the class. The association rules that the judge must place 6 out of the class of 17. When you enter the placeings 1 thru 6 it calculates the points correctly. The other llamas shown get no points. So if the "placed" field for the other llamas is left blank it awards 6 points. These llamas that don't place are dismissed from the show ring and are not placed. However all llamas not placeing beyond 6th are given 6 points in the ALSA field. My question is if the other 9 llamas did not place how do I get the ALSA points to be 0 or blank.

    I have attached my data base as crude as it is if you care to look at it. You will find the examples under Show ring entry, also attached the place.doc for award points.

    I also think that I have post this question in the wrong section. I am using Alpha5 v8 Pro

    Thanks
    Jim

    If you can help if would be appreciated
    I don't have V8 so I can't open parts of your database that use specific new features.

    As to the logic of your question, can you restate it. I got lost with 6, of 17 and then "tho other 9 llamas". Doesn't add up for me.

  20. #20
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Sorry that should be other 11 llamas that did not place.
    Jim

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Sorry that should be other 11 llamas that did not place.
    Jim
    I think I'm still lost. If you want 0 points for all llamas that do not have a placed value....

    Code:
    case(placed = 0,0,between(tablecount("llama data","Classid = "+Classid),3,4).and.placed = 1,max(placed,0),tablecount("llama data","Classid = "+Classid)=5,max(3-placed,0),tablecount("llama data","Classid = "+Classid)=6,max(4-placed,0),tablecount("llama data","Classid = "+Classid)=7,max(5-placed,0),between(tablecount("llama data","Classid = "+Classid),8,15),max(6-placed,0),between(tablecount("llama data","Classid = "+Classid),16,23),max(7-placed,0),between(tablecount("llama data","Classid = "+Classid),24,31),max(8-placed,0),between(tablecount("llama data","Classid = "+Classid),32,39),max(9-placed,0),tablecount("llama data","Classid = "+Classid)>39,max(10-placed,0))

  22. #22
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Yes, all llamas that do not have a value in place field will have a 0 in ALSA_points field.

    If I am clear where is the code in to last reply put in relation to the code for awarding points?
    Jim

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Yes, all llamas that do not have a value in place field will have a 0 in ALSA_points field.

    If I am clear where is the code in to last reply put in relation to the code for awarding points?
    Jim
    That expression replaces the previous suggestion. It merely checks first to see if the placed field is empty and awards 0 points. After that it maintains the previous calculation.

  24. #24
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    Stan,
    The last code worked perfect. The saying about teaching an old dog new tricks can apply to me. I am 67 and and enjoying every minute of learning new tricks. I am obsessed with Alpha5 v8.

    Thanks again for your help
    Jim

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

    Default Re: Can anyone help me with this expression?

    Quote Originally Posted by jdoyle View Post
    Stan,
    The last code worked perfect. The saying about teaching an old dog new tricks can apply to me. I am 67 and and enjoying every minute of learning new tricks. I am obsessed with Alpha5 v8.

    Thanks again for your help
    Jim
    And you hang out with a bunch of odd looking fuzzy animals. Pluses and minuses in everyones life, I suppose.

  26. #26
    Member
    Real Name
    Jim Doyle
    Join Date
    Dec 2006
    Location
    Krum, Texas
    Posts
    213

    Default Re: Can anyone help me with this expression?

    what can I say.
    Check us out at www.marikollamas.com
    Jim

Similar Threads

  1. Need Expression Help
    By MortieM in forum Alpha Five Version 6
    Replies: 7
    Last Post: 10-27-2005, 12:01 AM
  2. Expression Help
    By Bill@TrackerSystems.com in forum Alpha Five Version 6
    Replies: 7
    Last Post: 11-09-2004, 11:20 AM
  3. Expression Help
    By Tom Patten in forum Alpha Five Version 4
    Replies: 6
    Last Post: 10-06-2003, 09:55 AM
  4. Expression Help, Please
    By Jeri Grantham in forum Alpha Five Version 5
    Replies: 2
    Last Post: 05-23-2003, 01:46 PM
  5. Expression
    By Ed Barley in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-23-2002, 02:21 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
  •