Alpha Video Training
Results 1 to 25 of 25

Thread: I need Calculated Field Expression Help

  1. #1
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default I need Calculated Field Expression Help

    I created a grid of jobs to be worked on, so we can keep track of everything. The SQL database I am linked to stores our rep names by number. For example 1 = John Smith. So on my grid it shows up as the number 1. I created the if statement (below) that does do what I want but when you click on a job and see a detailed view the field goes blank. No number or name. Can someone please advise.

    if(CSR=3, "JJ", if(CSR=11, "MJS", if(CSR=18, "DD", if(CSR=54, "MH", if(CSR=55, "DF", if(CSR=59, "MEP", if(CSR=64, "MLS", if(CSR=122, "LD", ""))))))))


    Thanks in advance!

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

    Default Re: I need Calculated Field Expression Help

    Hi Fernando,
    I'm not sure where you're using this code.
    With that said, I am assuming the sql table contains the csrID as well as the csrName.
    In my opinion it would be far easier to have csr and csrName in the query, then simply show the csrName where you want it.

    Showing the grid with some generic data makes it a lot easier to help solve the problem.

  3. #3
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Hi Madtowng,
    How would I go about doing that? Yes my sql table contains the csrID and the csrName. I am putting the code on the calculated field expression under Client Side Properties for the CSR field that I have selected to show on the grid. At the moment, as a work around I have setup a Javascript - System event: afterDetailViewClose {grid.Object}.refresh(). Which works but once it refreshes it takes you back to the top. Which isn't much of a big deal but not working as it should.

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

    Default Re: I need Calculated Field Expression Help

    At a bare minimum, show us the query section and fields section from the grid setup
    as shown in the images below. This will provide some real information to help with
    the issue.

    2019-08-08_6-29-49.png2019-08-08_6-31-13.png

  5. #5
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Here you go. Thank you!

    Capture1.PNGCapture2.PNGCapture3.PNG

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

    Default Re: I need Calculated Field Expression Help

    Is salesrep the column with the name for CSR ?

  7. #7
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Yes that s correct.

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

    Default Re: I need Calculated Field Expression Help

    I think you just need to add salesrep to the detailview fields.
    You shouldn't need the if statements.

  9. #9
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    This is what I see when I open the grid and what I should always see. Capture1.PNG
    But after clicking on the row and viewing the detail view and closing. I get this. Capture2.PNG
    As you can see the salesrep disappears. I don't get why.

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

    Default Re: I need Calculated Field Expression Help

    Ok...try this
    Add salesrep column to the grid (either side of csr).
    remove the if statement for csr column.
    check the box to hide csr column.

  11. #11
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    No luck. Now all I see are the actual numbers. For example, the picture i posted earlier, where the CSR was MEP is now showing 54.

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

    Default Re: I need Calculated Field Expression Help

    I'm hoping you see the salesrep as well.
    It sounds like the csr column is not hidden.
    Check the image below to see how to hide the csr column.

    2019-08-08_15-55-58.png

  13. #13
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Thank you for helping and trying to get me through this. Unfortunately the result was always the same. I do appreciate you for your time. Thanks!

  14. #14
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,115

    Default Re: I need Calculated Field Expression Help

    you should try a switch case statement instead.
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

  15. #15
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Good morning Charles. Thanks for the response. I tried that as well and I get an error message. Something about not being able to be parsed?

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

    Default Re: I need Calculated Field Expression Help

    I'd be willing to do the testing myself if you provide the test data and the grid file.

  17. #17
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,115

    Default Re: I need Calculated Field Expression Help

    If it cannot be parsed, there is an error in the javascript, post your switch case statement and surely someone will spot the error
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

  18. #18
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Unfortunately I can not send you you any test data but here is (hopefully a better) explanation of what I need to accomplish.

    My database consists of:

    OrderID = Order Number
    BillToCompany = Company being billed
    ProjName = Name of the project
    FirstDrop = Drop date
    CSR = Customer Service Rep
    UserDefDate7 = Date we received the job
    UserDefDate8 = Estimated date of completion
    DPStatus = Status of the job
    DPInitials = Initials of person working on the job
    EnterDate = Date job was submitted
    MaterialIn = Date material was received
    DateDataChecked = Processing completed date

    Everything works perfectly. Except for the fact that the CSR field being pulled in is a number instead of the actual customer service reps initials. That's why you see me using an if statement converting the numbers to the actual initials. As I have been looking into this, I see my datasource is tblOrders and it seems to be linked to tblEmployee which has EmployeeID (the number I am seeing) and Initials (which is what I need). Unfortunately it isn't in the current datasource I am using. Which is giving me this issue.

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

    Default Re: I need Calculated Field Expression Help

    Hi Fernando,

    The data source would certainly explain the issue.
    The solution could be as simple as creating a view to include the csr column from the tblEmployee table, or a sql select
    statement that would do the same thing.
    The code shown below should add a column named Initials which would then give the functionality you're looking for.

    Code:
    select o.OrderID,o.BillToCompany,o.ProjName,o.FirstDrop,o.CSR,o.UserDefDate7,o.UserDefDate8,o.DPStatus,o.DPInitials,o.EnterDate,o.MaterialIn,o.DateDataChecked,e.Initials
    from tblOrders o
    left join tblEmployees e on e.EmployeeID = o.CSR

  20. #20
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Thanks Gregg! I will give this a try. Where will I be inserting the code?

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

    Default Re: I need Calculated Field Expression Help

    Hi Fernando,
    That code needs to go in the SQL Select Statement area as shown in the image.
    If you don't want to mess with that, it should work in your favorite SQL editor (I use Toad, many use Navicat).

    2019-08-14_8-50-32.png

  22. #22
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    Will this mess with my filter? I need that in order to show the correct dates. 5 previous days and 15 days in the future.

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

    Default Re: I need Calculated Field Expression Help

    I can't see the complete filter, but this code should work. You might want to try it in your favorite editor or even AA's SQL Command window.
    Code:
    select o.OrderID,o.BillToCompany,o.ProjName,o.FirstDrop,o.CSR,o.UserDefDate7,o.UserDefDate8,o.DPStatus,o.DPInitials,o.EnterDate,o.MaterialIn,o.DateDataChecked,e.Initials
    from tblOrders o
    left join tblEmployees e on e.EmployeeID = o.CSR
    where <put filter here>
    order by firstdrop

  24. #24
    Member
    Real Name
    Fernando Cruz
    Join Date
    Jun 2019
    Posts
    25

    Default Re: I need Calculated Field Expression Help

    It worked beautifully. Thank you so much! I would have never gotten it done with out you. I appreciate it very much.

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

    Default Re: I need Calculated Field Expression Help

    Glad to hear we finally got it working.

Similar Threads

  1. Calculated Field Expression Help
    By Gustavo1478 in forum Mobile & Browser Applications
    Replies: 12
    Last Post: 12-29-2014, 12:26 PM
  2. Calculated Field Expression that includes auto increment ID field...
    By dickerman7239 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 4
    Last Post: 07-04-2011, 05:12 PM
  3. calculated field expression
    By Kamel in forum Application Server Version 10 - Web/Browser Applications
    Replies: 17
    Last Post: 06-26-2011, 11:56 AM
  4. Calculated field expression
    By Lu Peterson in forum Alpha Five Version 5
    Replies: 9
    Last Post: 11-18-2003, 06:22 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
  •