IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)  Topic is solved

Support for Crystal Reports within Jiwa.

IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)

Postby DannyC » Wed Jun 06, 2018 6:13 pm

Am doing a fairly simple (or so I thought) modification to MNT40001- Purchase Order to show Quantity as the UoM quanity and Unit as the UoM unit name.

A join (LEFT OUTER) from PO_Lines to IN_UnitOfMeasure in the report results in an error when trying to print preview.
I am suspecting it is because the RecID is uniqueidentifier and the CR previewer doesn't like it. That my suspicion as I've done these kind of joins hundreds, possibly thousands of times so I'd like to think I know what I'm doing. But as far as I know, this is the first time a RecID has been designed as uniqueidentifier.

What tweak do I need to do to use the IN_UnitOfMeasure in reports such as MNT40001? Ideally I just need to check that if a UoM exists i.e IsNull(PO_Lines.IN_UnitodfMeasure_RecID) or alternatively IsNull(IN_UnitOfMeasure.RecID) then use the UoM values for Quantity and Unit, else just use the existing PO_Lines values.

I guess the most obvious solution is to completely change the report source to a stored procedure?

(move the post to the Crystal Reports forum if more appropriate there).
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)

Postby Scott.Pearce » Fri Jun 08, 2018 11:48 am

I tested this and reproduced the problem in 7.0.175.0, but the same report worked OK in 7.1 and above. I suspect Crystal fixed this issue in one of their run-time service packs along the way.

In which version of Jiwa are you seeing this problem, and can you confirm your report works OK in v7.1?
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)

Postby DannyC » Fri Jun 08, 2018 7:18 pm

In which version of Jiwa are you seeing this problem


It's in 7.0.175.
The client isn't on 7.1 yet & likely will not for some time.
It's fairly urgent for them.
Is there some way we can incorporate the latest Crystal runtimes into 7.0.175 if that's the issue?

Cheers
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)

Postby Mike.Sheen » Sat Jun 09, 2018 12:42 am

DannyC wrote:It's in 7.0.175.
The client isn't on 7.1 yet & likely will not for some time.
It's fairly urgent for them.
Is there some way we can incorporate the latest Crystal runtimes into 7.0.175 if that's the issue?


Patching 7.0.175 with the Crystal runtime that 7.1 uses is not a viable option.

Creating a view of IN_UnitOfMeasure which casts the RecID to CHAR(36) and then using that view instead of IN_UnitOfMeasure in your report will work, and should only take you a few minutes of effort.

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: IN_UnitOfMeasure.RecID uniqueidentifier vs char(36)  Topic is solved

Postby DannyC » Sat Jun 09, 2018 11:32 am

I thought of that too when I realised the issue, but when I linked the view into the report, Crystal still spat the dummy hence I raised this post.
In my view (SQL view, not opinion) I am also testing for IsNull.
It displays fine in SQL, but Crystal doesn't like it.
Code: Select all
ISNULL(CAST(dbo.IN_UnitOfMeasure.RecID AS varchar(36)), 'NoUOM')

Maybe I should cast to char instead of varchar? I'll give it a whirl but I don't think it'll make a difference.

Well, whodathoughtit! Changing from varchar to char seemed to do the trick - at least on my demo data. And it works on client site too!
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29


Return to Crystal Reports

Who is online

Users browsing this forum: No registered users and 1 guest