Solved

Relate data without using SQL

Posted on 2011-02-10
8
240 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
[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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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