Crystal Report forcing combination of fields

    Hi! I am in full blown gray matter gridlock!
    I have this report I created in Crystal Reporting (V. 8.5.0299) that I use with Mas90. The problem is (Mas90) (stinketh mightily) (sorry) the fields for the Division, Vendor Number and Vendor Name. The Division and Vendor Number are seperate entities in the area of Mas90 that I am pulling totals from. However, they are combined in other areas of Mas90 that I am referencing to get the Vendor Name...I think.
    For Example two vendors: Vendor 30-001172 and Vendor 00-001172. the "30" and the "00" are the Division and sometimes the rest of the number is identical. When I run my report, it shows the correct division "30" and correct Number following, but rather than give me the actual vendor name associated with the information given, it will print the one that starts with the "00". Since all the information that I need for my report is in one field of Mas90 EXCEPT the name, how can I get Crystal to look at the "VendorMaster" and return the correct name for each particular invoice?


    Not sure if that makes sense to someone not looking at it, and I apologize. Here is the SQL Query it it will help:
    SELECT
    APH_JobDistDetail."Division", APH_JobDistDetail."VendorNumber", APH_JobDistDetail."InvoiceNumber", APH_JobDistDetail."JobNumber", APH_JobDistDetail."CostCode", APH_JobDistDetail."CostType", APH_JobDistDetail."DistributionAmount",
    AP1_VendorMaster."VendorName"
    FROM
    "APH_JobDistDetail" APH_JobDistDetail,
    "AP1_VendorMaster" AP1_VendorMaster
    WHERE
    APH_JobDistDetail."VendorNumber" = AP1_VendorMaster."VendorNumber" AND
    APH_JobDistDetail."JobNumber" = 'CATS'
    ORDER BY
    APH_JobDistDetail."CostCode" ASC



    any help will be greatly appreciated! Thanks

    You can tell the quality of a person by how they treat people they don't need

    Quote from kimberly

    The Division and Vendor Number are seperate entities in the area of Mas90 that I am pulling totals from. However, they are combined in other areas of Mas90 that I am referencing to get the Vendor Name...I think.


    Is the format of the following fields


    APH_JobDistDetail."VendorNumber",
    AP1_VendorMaster."VendorNumber"


    1. identical ?
    2. in the "combined" format of "00-001172"


    is AP1_VendorMaster the reference table that contains all the Vendor details... Sorry for the questions, but not knowing your database I think we need a little more detail in order to see how these tables are supposedly related. It may be that the way you are linking them is incorrect. Do you have any documentation that tells you how AP1_VendorMaster is indexed ?

    Yes the Vendor Master is the one that holds all the information relating to each vendor, including the last check, invoice, current balance. But it doesn't show history. That is in the APH_JobDistDetail and several other tables.
    I am trying to locate some indexing documentation, so far just finding the correct table has been trial and error. I will update this as soon as I find the info. THANK you for helping :) this forum is the best.

    You can tell the quality of a person by how they treat people they don't need

    What is the list of fields in table Vendor Master ?


    Is there a "Division" Field ?


    If so, the tables may in fact be indexed on a combination of "Division" & "Vendor Number" (some databases do this)


    If this is the case, you may need to alter the join to something like this


    [vba]
    WHERE
    (APH_JobDistDetail."VendorNumber" = AP1_VendorMaster."VendorNumber" AND
    APH_JobDistDetail."Division" = AP1_VendorMaster."Division" AND)
    APH_JobDistDetail."JobNumber" = 'CATS'
    [/vba]


    Hope this helps

    Altered the join and got this error code:
    ODBC error: [Providex][ODBC Driver] Expected lexical element not found: )


    followed by the ever popular: Error detected by database DLL.


    (I am able to recognize that as English, but as to the meaning of the words.....sigh) :) Any ideas?

    You can tell the quality of a person by how they treat people they don't need

    Sorry it took so long to reply. I found something distrubingly interesting. In the fields of the Vendor Master, "Division" is listed...but when you try use the select expert, only the division "00" is there. Now I am at a total loss!

    You can tell the quality of a person by how they treat people they don't need

    Hmmm okaaaaay.. :(


    So with the Vendor "30-001172"


    If the ID is as you say, an amalgamation of a 2 digit "Division" id and the 6 digit vendor id...and there is only a "Division" id of "00"... um where does division 30 come in ?

    Sorry, I am still researching this. The division IS part of the vendor number, so if the Vendor Master is only recognizing the division of "00" that explains why it is attaching that division to the account numbers for division "30" and the rest of them. I am still trying to figure this out, sorry.

    You can tell the quality of a person by how they treat people they don't need

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login