Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.


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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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