Solved

formula

Posted on 2011-02-24
8
422 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
[X]
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
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34977196
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
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.
0
 
LVL 35

Expert Comment

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

 James
0
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
LVL 7

Author Comment

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

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
 
LVL 7

Author Comment

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

Accepted Solution

by:
James0628 earned 350 total points
ID: 34986744
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
ID: 35013681
Thanks James
0
 
LVL 35

Expert Comment

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

 James
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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