List to show all the vendors of the different companies

shivaprasad263

Can I create a Vendor list, to show all the vendors of the different comapnies with in the database?


Thanks,
Prasad

Nagi

Yes, this is quite easy. Create a report with a temporary table and fill this table with entries in the Vendor table from the different companies. You can use the record variable Company and the CHANGECOMPANY function to loop through the different records in the Vendor table for each company. You may have to copy the No. field to a different field in the temporary table and just use a dummy variable to fill that field, so that you don't get "Record already exists" error when inserting in the temporary table.

The code would be something like this...

DummyInteger := 0;
VendorTemp.RESET;
VendorTemp.DELETEALL;
Comp.RESET;
IF Comp.FINDSET THEN
  REPEAT
    Vendor.RESET;
    Vendor.CHANGECOMPANY(Comp.Name);
    IF Vendor.FINDSET THEN
      REPEAT
        DummyInteger += 1;
        VendorTemp.INIT;
        VendorTemp.TRANSFERFIELDS(Vendor);
        VendorTemp."Name 2" := Vendor."No.";  //Or some other field you won't need to show in your report
        VendorTemp."No." := FORMAT(DummyInteger);
        VendorTemp.INSERT;
      UNTIL Vendor.NEXT = 0;
  UNTIL Comp.NEXT = 0;

kine

Or if you are on MS SQL, you can create new View which will do union of all the tables and you can than connect this view into NAV as LinkedObject and access it as any other table... (but please, do it just for read-only)

shivaprasad263

Thanks for both of you. Is there any way to do, without using Temp table.

bbrown

shivaprasad263 wrote:

Thanks for both of you. Is there any way to do, without using Temp table.

See the post above yours (if on SQL)

vanrofi

Some would advise to share the table over all companies...it's just a property. BUT NEVER DO THAT on this kind of tables.

Just follow the advises above, I think these are best.

shivaprasad263

vanrofi wrote:

Some would advise to share the table over all companies...it's just a property. BUT NEVER DO THAT on this kind of tables.

Just follow the advises above, I think these are best.

Thanks for your suggestion. It helps me to know about one new propety.

garak

you can create a view in your dateabase if you work on sql server. then you create a table in navision and set property linkedobject. See also in documents on CD/DVD there are examples. Also note, if you use a view. If you create / delete / modify a company you must also alter your view (the select statement in the view), but these is not a problem and very easy

garak

if you are in native or you wan't use a view you can use a temp. table or a real NEW table (Datapercompany = no). These table you fill / delete / modify if a vendor is created / modified in the companies.

regards

knight

I think the approach I would use, without the need of a temporary table is to make a report with two dataitems.

Company
-> Vendor

Vendor is nested under the Company table.

Then on the PreDataItem of the Vendor dataitem, put the following code:
Vendor.CHANGECOMPANY(Company.Name);

Then on the Vendor Body sections, just place you normal vendor related fields.

If you want, you can even put some fields on the Company Body section, and that way you can differentiate which company the vendors are linked to.