Solved

formula

Posted on 2011-02-24
8
408 Views
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!
formula.bmp
0
Comment
Question by:mcse2007
  • 4
  • 3
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Is this a row to row compare?

How is the place hjolder value determined?

Where does the place holder data come from?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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.

 James
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
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).

Aim:
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!


text-position.xls
transport.xlsx
shipment-gmk-20101221-081002-030.txt
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Author Comment

by:mcse2007
Comment Utility
My problem is how on earth Am I going to join the two files together using CR?  Impossible?
0
 
LVL 34

Accepted Solution

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

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26835601.html


 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.

 James
0
 
LVL 7

Author Closing Comment

by:mcse2007
Comment Utility
Thanks James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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.

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now