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.
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?
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?
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!!
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
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?
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