Posted on 2011-02-24
Last Modified: 2012-05-11
Hi Guys,

Is it possible to write a formula in CR with the below condition: Pls see also the enclosed image also.

If record in column "code" from table, doesn't equal what's in the formula place holder,
show the record from the address column from table

Answer: 203 Albany Hwy

if yes, how do I create the formula which is a place holder.

Why do I need this formula? The information from the formula place holder, is not in the same table or no join table existing, in other words, it is isolated data.

I'm trying to create a placeholder formula or something similar so I can use the information which could have 700 rows (e.g., company, delivery address, state, postcode)

Or, do you have any other suggestion?

Thanks a lot!
Question by:mcse2007
  • 4
  • 3
LVL 100

Expert Comment

ID: 34977196
Is this a row to row compare?

How is the place hjolder value determined?

Where does the place holder data come from?


Author Comment

ID: 34977228
It will be a row to row comparison.

My problem at the moment is I have a separate data (company, address, postcode, etc) in excel which is separate to the main table and no join table existed because the original source data is TXT file.

Is there any way I can use this separate data (e.g. create a formula) then do a condition formula row to row as explained above.

I'm still formulating my placeholder...I don't think it can even be done. The would be placeholder information would come from the separate data...which is not joined.

My aim is simply, do a condition formula row from table to row from separate data......not sure how it can be done?

thanks again.
LVL 34

Expert Comment

ID: 34977790
You _might_ be able to use a LEFT OUTER JOIN from the original file to the "place holder" file on the Code column, and use the record selection to only include the records where the Code column in the "place holder" file is null, meaning that no matching Code was found in the "place holder" file.  That could work, depending on your files, etc.

 Another option might be to use a subreport to read the "place holder" file and store the codes in an array, and then compare the Code's from the main file with the values in the array, based on the record number.  Arrays are limited to 1000 elements.  If you have 700 rows, then that's not a problem.  But if you could have more than 1000, that would complicate things, but it could still be done.

 The first option (joining the files) seems simpler to me, so I'd suggest that you try that first.

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


Author Comment

ID: 34978240
i apologise in advance...i should have been more precise and provide more samples on this post...apologies again.

Here's what I'm trying to achieve and not sure if it can be done.

My source file is a TXT file (see enclosed) whereby the data are predetermined and have predetermined character position (see enclosed file).

Enclosed is the transport record saved in XL (e.g., customer code, transport company name etc).

From the TXT source file, on some given days, this file could end up having multiple rows.

What I'm tring to match is the customer code (e.g. 115228) from the TXT source file with the customer code from the transport XL file. If the customer code matched, I will use the transport details address from the transports XL file. If not, I'll use the delivery address from the the TXT source file.

My problem is how on earth Am I going to going the two files using CR?  Impossible?

Thanks James from your early post...I've learned some ideas again.

Thanks a lot!


Author Comment

ID: 34978244
My problem is how on earth Am I going to join the two files together using CR?  Impossible?
LVL 34

Accepted Solution

James0628 earned 350 total points
ID: 34986744
Ah, OK.  For reference, this is the same text file referenced in this question:

 You won't be able to join that text file with anything else in CR, since it has just one field containing the entire line.

 You could use a report as described in the other question to export the text file to a comma-separated (CSV) file, and then read the CSV file in this report; or import the CSV file into Excel and use that Excel file in this report.  Then you could theoretically link that file to the Transport Excel file.

 Or, you could try the array option, similar to what I described before.  A subreport in the report header of the main report would read the Transport Excel file and store the customer codes in one array, and whatever other data you needed from that file (name or address or whatever) in other arrays.  Then the main report would read the text file, extract the customer code from each record (using Mid), and check to see if that code was in the first array.  If so, it would use the data from the corresponding elements in the other arrays.  If not, it would use the data from the text file.

 As I mentioned before, arrays are limited to 1000 elements, so if there could be more than 1000 rows/customers in the Transport Excel file, that will complicate things, but apart from that, it seems straightforward enough.  Can't say how well it will perform, but it seems like it should work.

 Converting the text file into a CSV text file, or an Excel file, involves some extra steps, but the final report should be simpler (not having to deal with the arrays), and might perform better (Or not.  It's hard to say).  FWIW, if you have access to a db, like Access or MS SQL, loading these files into tables would probably greatly improve the report's performance, but, again, that involves some additional steps.

 It's your call which approach you want to try.


Author Closing Comment

ID: 35013681
Thanks James
LVL 34

Expert Comment

ID: 35014859
You're welcome.  Glad I could help.  And, of course, if you want help with a specific approach, you can always ask another question.  I didn't want to try to get into the details until I knew which direction you wanted to go.


Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question