Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Relate data without using SQL

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
mowit
Asked:
mowit
  • 5
  • 3
1 Solution
 
Kurt ReinhardtCommented:
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
 
mowitAuthor Commented:
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
 
Kurt ReinhardtCommented:
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
mowitAuthor Commented:
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
 
Kurt ReinhardtCommented:
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
 
mowitAuthor Commented:
I can't upload sorry. I'll just resolve by modifying the report object. Thanks for you help.
0
 
mowitAuthor Commented:
Figured it out i was using the wrong descriptions field.
0
 
mowitAuthor Commented:
resolved on my own.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now