Improve company productivity with a Business Account.Sign Up

x
?
Solved

Relate data without using SQL

Posted on 2011-02-10
8
Medium Priority
?
256 Views
Last Modified: 2012-08-13
I'm pulling data from XML/ XSD. Here is my challenge.
I have ID,Full Name,Title fields. I need to display the Title if the name isn't present. How to relate this back using the ID field. I don't see any options for SQL formulas.  Bellow is what I have so far but I need to relate the ID field otherwise it's just blank.

If IsNull({RateExcDet_NoActions.Timekeeper1__SortName}) Then
{RateExcDet_NoActions.Timekeeper1__TkprDate__Title1__Description}
Else
{RateExcDet_NoActions.Timekeeper1__SortName}
0
Comment
Question by:mowit
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865615
What do you mean by "relate"? To me, it means you're pulling data from two or more tables and you're trying to link the tables on a related field.  In your case, you seem to be pulling all fields from one data source. Please note, Crystal is designed to pull data from table-like structures and it doesn't like inconsistency.  So, even when you're using XML as a data source the output should be a consistent table format.  In other words, the result of the xml should be the same columns in the same order every time.  If a tag is present when you build the report, but isn't present the next time you run the report, that's a problem.  

So, what's going on with your XML?  Is it a standard table structure?  Are all the fields available every time you use it?  Are all of the fields in the same XML (meaning it's being treated like a table, you're not joining multiple sources)?

~Kurt
0
 

Author Comment

by:mowit
ID: 34865673
I'm using XSD as the data source. The XSD is created by a report object designed to work with Crystal Never mind its difficult to explain. I can resolve this by making changes to my report object, i just wanted to know if there was a way to do it in crystal. I was said "relate" i was referring to one table relating to itself.

0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865839
OK - so you really don't want to relate anything at all.  You simply want to display one value if another is missing, correct?

If so, your formula should work, but only if the Timekeeper1__SortName is actually NULL. If it's an empty value, the NULL check won't work.  So, you might want to modify it slightly:

If
  (
  IsNull({RateExcDet_NoActions.Timekeeper1__SortName})
  or Trim({RateExcDet_NoActions.Timekeeper1__SortName}) = ''//just in case there are spaces
  )
Then
  {RateExcDet_NoActions.Timekeeper1__TkprDate__Title1__Description}
Else
  {RateExcDet_NoActions.Timekeeper1__SortName}

~Kurt
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

by:mowit
ID: 34865875
Kurt,

I did this in my formula above but it's not working, just shows up blank. its like i need to tell it to look at the id field then show me the title for that id.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865958
You shouldn't have to, since that's not how Crystal works.  It doesn't matter if you're using an actual table or XSD - it reads all of the fields and data into an object.  If you can see it under a single database in your database field explorer, then  you should be able to do it  Sort Name and Description are just fields in a single record.  Can you upload a copy of the report with saved data (assuming it's not confidential)?

~Kurt
0
 

Author Comment

by:mowit
ID: 34866146
I can't upload sorry. I'll just resolve by modifying the report object. Thanks for you help.
0
 

Accepted Solution

by:
mowit earned 0 total points
ID: 34866389
Figured it out i was using the wrong descriptions field.
0
 

Author Closing Comment

by:mowit
ID: 34895069
resolved on my own.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

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. …
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…
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…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…

595 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