Solved

Relate data without using SQL

Posted on 2011-02-10
8
237 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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