Posted on 2011-02-24
Medium Priority
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 101

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 35

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.

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 35

Accepted Solution

James0628 earned 1400 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 35

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

607 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