I have a Table with a list of Equipment Items. The equipment field also has 3 possible Vendors Fields (Vendor1, Vendor2, Vendor3) that this equipment may be purchased from. Some equipment may have only 1 vendor inputted and some may have 2 or 3 etc.

I have designed a Request for Quotation template to be sent to each individual Vendor for a quotation. But the problem is that i can only place Merge fields in the report template for equipment and vendor1.

How do i place Merge fields for seperate Reports for Vendor2 and Vendor3 without copying the entire Template 3 times in the same report and input Vendor2 and Vendor3 Merge fields etc.

As some Equipment may have only 1 associated Vendor with it, the entire Report with Vendor2 and Vendor3 templates are printed also.

Anyone face this issue? Any help is appreciated.

Sully

Well, you should have a join table btw equipment and vendors that stores the possible combinations since it's a many to many relationship. The report would be designed based on the join table.

Hi BCooney,

Thanks for reply.

Can you give me an example please. Is this what you mean below? But how can i get the equipment report to print for each vendor etc. Can you please give me an example so i can try and figure it out in my head.

Equipment Table

PK_Equip_id

Equipment

Vendor1

Vendor2

Vendor3

Join Table

fk_Equip_id

fk_Vend_id

Vendor Table

PK_Vendor_id

The vendor1-3 fields go away completely.

You would print a subsummary by vendorID report fro the Join table.

Thanks Barbara

I think i'm understanding a bit now.

Would it be like below?

Equipment Table

PK_Equip_id

Equipment

Join Table

fk_Equip_id

fk_Vend_id

Vendor Table

PK_Vendor_id

Vendor_Name

I forgot to mention that there is an RFQ number assigned to the equipment like below.

Equipment Table

PK_Equip_id

Equipment

RFQ_NO

Join Table

fk_Equip_id

fk_Vend_id

Vendor Table

PK_Vendor_id

Vendor_Name

How can i now get a list of RFQ's per equipment per vendor?

I.e.

Product 1 has an RFQ No. 001 and possible Vendor = Vendor1

Product 1 has an RFQ No. 001 and possible Vendor = Vendor2

Product 1 has an RFQ No. 001 and possible Vendor = Vendor3

Can i configure the Join table somehow to then do a Subsummary of RFQ Nos? My template has 4 pages for each vendor and will the subsummarys per each vendor be seperate to send to each vendor individually etc.

Does each equipment record have many RFQs? Is RFQ a separate table? Sounds like it should be. I'm imagining that you create a new RFQ (possibly from the equipment form) and fill out whatever you need on the RFQ record. It relates to one and only one equipment record. Is this true?

So, we'll need an RFQ for each time you request one for the equipment, and for each vendor. I'm starting to think you'd fill out an RFQ using a layout that's globals and a script would generate RFQ records for each vendor of this equipmentid. (Each RFQ will have a fk_equipid and fk_vendorid. Then would report from the RFQ table by Equip by Vendor.

Good of you take turns here Barbara, I actually promised to remake my take on what I supposed the RG should look like, only a little shortage of time prevented me from....

http://www.fmforums.com/forum/showtopic.php?tid/193087/post/280560/hl//fromsearch/1/

--sd

Wow, Soren, I didn't even know this other thread existed. I'll stay out of it, too many cooks.

No please do take part in it!!! It's quite a handful as it is ... I have no idea of where the relational concepts used originates from, it seems to be ERD but I'm not sure!

--sd

I'm starting to think you'd fill out an RFQ using a layout that's globals and a script would generate RFQ records for each vendor of this equipmentid.

Not necessarily globals in plural, as well as not really necessarily scripted - although convenient!

http://www.fmforums.com/forum/showpost.php?post/149069/

...and if scripting, something in the vicinity of this:

http://fmforums.com/forum/showpost.php?post/280360/

--sd