Skip to Content
author's profile photo
Former Member

query on a formula field

Post Author: llcoolt

CA Forum: Formula

I need help with a formula returning a value and then relating tat result to another field.

example:

the formula for my "ConstEnd" is

{SCHEDHOUSEDETAIL.ACTIVITYCODE}="962"

In each of my records, the activity coe 962 has a date, which is what I need.

Any help would be helpful!!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • author's profile photo
    Former Member
    Posted on Oct 01, 2007 at 04:17 PM

    Post Author: foghat

    CA Forum: Formula

    I'm not exaclty sure what you are trying to do. If you are looking for dates where the activity code = 962, you can create a second formula or take your forumula one step further:if {SCHEDHOUSEDETAIL.ACTIVITYCODE}="962"then totext({date.field})else '' If you only want to return records where {SCHEDHOUSEDETAIL.ACTIVITYCODE}="962" then you can put this logic in the record selection formula editor, add the date field to the report and you are good to go.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 03, 2007 at 06:29 AM

    Post Author: llcoolt

    CA Forum: Formula

    Thank you for your response. To clarify, each record (lot) has MANY activity codes and I want to pull the date (if there is one) from the activity code #962. This is not a selection criteria, but a field in the report.

    I tried the formula that you suggested, but each and every actvity code appeared on each and every record (lot). Any thoughts?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 03, 2007 at 02:22 PM

    Post Author: foghat

    CA Forum: Formula

    The 'if then' formula should work for you. Or maybe I still don't understand what your data is looking like and exactly what you want. Can you post a sample of what the data currently looks like and what you would like it to look like?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 06, 2007 at 08:44 PM

    Post Author: llcoolt

    CA Forum: Formula

    Okay. I have another formula that is trying to read the outcome of another. This might answer the question for both issues.

    I have a field that returns a 5 digit number (COSTCODE), which in most tables I reference is split into a 3 digit number field (CATEGORYCODE) and a 2 digit number field (COSTCODE). For some reason, the field I need to pull from on this particular table has combined them so that (COSTCODE) in one table cannot link to (COSTCODE) in another. In this report I want to reference the last two digits ONLY. Those two digits are a code that references a decription (COSTCODEMASTER.DECRIPTION ). To return just the last two digits that I need, I wrote this formula (easy enough) which gives me what I want:

    mid({PODIST.COSTCODE},3,2)

    So, I had a costcode "40780". With the formula mention above, I get "80", which is what I want. Now I want to reference another table/field that will give me description of "80", which in this case is "Field Error". How do I link the formula result to the table/field, COSTCODEMASTER.DECRIPTION ?

    Your help is GREATLY appreciated!!

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 07, 2007 at 01:26 AM

    Post Author: yangster

    CA Forum: Formula

    you would have to write an if or a case statement to return your desired resultsthis is because what you've done needs to be done first so its not like you can rejoin it back to a tableit needs a literal referenceif blah = 80 then "field error" elseif blah = 90 then "some other error" etc etcthe smarter and better approach would be to do the joins in sql, that way you could strip the field and pull in the description by doing joins to actual tables

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 07, 2007 at 04:26 PM

    Post Author: llcoolt

    CA Forum: Formula

    I like the sound of the smarter and better approach, but am unlearned in that area. To bigin with, I would assume that I export the required tables/fields. From there, where do I go?

    Did you read the first issue in my post? Although it is similar, I need to day that if activity code 962 has a date, return the date. Any thoughts?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Oct 08, 2007 at 12:47 AM

    Post Author: yangster

    CA Forum: Formula

    what db are you reporting off of?your if statement can be easily captured in a case statement within the sql quearyif you have an example of data on how the tables are setup, writing the query is fairly straightforward

    Add comment
    10|10000 characters needed characters exceeded