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

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.

  • Moving this post to the Sage 300 CRE forum in the Sage Construction and Real Estate Support Group.

    Thanks,
    Derek

  • I would suggest writing a SQL Command Statement in Crystal Reports.  Within the statement, you should be able to use the MID Function to strip the SM out of the ARA_ACTIVITY_STATUS and create a join between the 2 tables.

    John McLagan
    Johnny on the Spot

  • 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