x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 242 subscribers
  • Views 2541 views
  • Users 0 members are here
Browse Forums
Sage 100 Contractor Announcements
119
topics
57
replies
Sage 100 Contractor General Discussion
1k
topics
4k
replies
Sage 300 CRE Announcements
168
topics
526
replies
Sage 300 CRE General Discussion
3k
topics
9k
replies
Sage Construction Management Announcements
4
topics
0
replies
Sage Construction Management General Discussion
16
topics
21
replies
Sage Estimating Announcements
39
topics
3
replies
Sage Estimating General Discussion
189
topics
448
replies
Frank Saucedo

Crystal Reports - Linking tables from Timberline SM Data to Timberline Data Source - one table has a prefix while other does not

SUGGESTED
Posted By

I am trying to create a crystal report which will allow me to reprint a batch of open/unpaid SM invoices using information from two different tables, one from the Timberline SM Data and the other from the Timberline Data Source. 

I have one table in Timberline Data Source which is ARA_ACTIVITY_STATUS that has an invoice number of "SM123456" and the other table in Timberline SM Data that has an invoice number of "123456" referring to the same invoice. The ARA_ACTIVITY_STATUS adds the "SM" and I can not link them going through the standard linking options. Is there any way to link these?

I have tried creating a report with a formula to remove the SM from one of the tables so that it can be linked to a subreport. However the subreport (the actual invoice) that I want to print is already using subreports and the second tier subreport is not printing data that is needed.

I am open to any suggestions.

Parents
  • 0

    If you prefer to not use the SQL method, you may be able to get away with doing the invoice part of the join in the selection formula of the Crystal Report.  You didn't specify which table you are using from SM, but assuming you are using the INVOICE table, you could first join the INVOICE table to the ARA_ACTIVITY__STATUS table using the ARCUST field on the INVOICE table to the "Customer" field on the Status table, and then include the following in the selection formula of the Crystal Report:

    "SM"+totext({INVOICE.INVOICENBR},0,"")={ARA_ACTIVITY__STATUS.Invoice}

    See if that works for your situation.

    Gary

Reply
  • 0

    If you prefer to not use the SQL method, you may be able to get away with doing the invoice part of the join in the selection formula of the Crystal Report.  You didn't specify which table you are using from SM, but assuming you are using the INVOICE table, you could first join the INVOICE table to the ARA_ACTIVITY__STATUS table using the ARCUST field on the INVOICE table to the "Customer" field on the Status table, and then include the following in the selection formula of the Crystal Report:

    "SM"+totext({INVOICE.INVOICENBR},0,"")={ARA_ACTIVITY__STATUS.Invoice}

    See if that works for your situation.

    Gary

Children
No Data

*Community Hub is the new name for Sage City