Relationship Help

[sullyman]

Please help me to figure out Relationships and see my attached pic and database file.

Here are my related issues. I think i have to put in "Link" Tables? but not sure how to (What fields to put in to them) and how to link them to overall tables.

1. I have unique individual Project Numbers associated with Clients although the Same Client may have different Project Numbers.

2. Individual Project Equipment would have unique Request for Quotations (RFQ) but some RFQ's may have the same Vendors.

3. Individual Projects would have unique Purchase Orders but some Vendors may exist in different PO Numbers.

Hope i am making sense above.

Sully

SullyFM.zip

[Søren Dyhr]

There is a awsome lot of misunderstandings in there, first on foremost is that only one of the keys should be autoenter not both, and here is it most common that the pk_ get's it.

Then are there missing a table line items.... then is there in my humble opinion an entity duplication between RFQ and Purchase order ... of course dependent on interpretation!!!

You need to read this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

...before going further!

I have above made a .jpg of RG as I would expect should look like!

--sd

[sullyman]

Thanks SD - I'm only starting out so thanks for help

[sullyman]

SD,

Can you tell me why you have two branches

i.e. Equipment 2 Vendor 2 etc.

Could you not have one table full of vendors that would have the ability to have more than one equipment products etc.

Thanks

[Søren Dyhr]

I can! Because of what I find a entity duplication would you have merged RFQ and Purchase but as such is there no need to have item lines twice since it's only a matter of the RFQ to get accepted, when the same lines get shown in the lower branch.

You have to study Mike Harris whitepaper to understand that the RG isn't showing entities but instead Table Occurences! This means that the same table can occure several times in the same graph, just with different facilitation of your data.

What else doesn't have to crystalized yet is that a field could be shown across several steps of relations but to avoid any filtering inplied by the linking must say Suplier data be present in both brances if you should need to show it in say a portal.

--sd

[sullyman]

Can you send me the FM file you created for the picture above so i can study this?

Presently we don't accept an RFQ like you explained. (Maybe we should) When we accept an RFQ at the moment, we then generate a corresponding PO Number with relevant document.

Thanks.

[Søren Dyhr]

NO it's ditched, I'll remake it tomorrow!

--sd

[sullyman]

Thanks SD

[bcooney]

I'm on WinXP and can't read this at all. I've tried dl and opening in Photoshop and it's tiny! Magnified it's pixelated. What's the deal?

[Søren Dyhr]

Well what I can't get is this:

When we accept an RFQ at the moment, we then generate a corresponding PO Number with relevant document.

To me isn't it a new document, just the lower branch with exactly the same data, just an attribute away ... not an import or duplication or anything in that direction.

Could you explain this???

--sd

[Søren Dyhr]

I've read further what you've said to Barbara, and this is my stab at at it ... some fields are obviously missing such as quantity as well as the price.

This time is hopefully blown big enough for all to investigate. When a RFQ is accepted is this calc' activating the lower branch:

Case ( Accepted_Boolean;List ( RFQ_Equipment::EquipmentLineID ) )

--sd

Edited February 10, 2008 by Guest

[bcooney]

Soren, where is the join table btw equipment and vendors? Also, I was trying to work thru the idea that he creates an RFQ for equipment and it generates one rfq record for each related vendor. What we really need is an ERD.

[Søren Dyhr]

This was what I attempted previously, but recieved a

or ...was it for the prevention of the entity duplication???

--sd

[sullyman]

Hi guys,

Thanks for the above. I only saw the replies now.

Thanks a million to try and help me figure this out.

I will try and draw an ERD Diagram if it will help.

Thanks,

Sully

[sullyman]

I have attached a basic diagram of the process that i need to achieve.

My issues are that some clients can be assigned several project numbers.

Also some vendors can be assigned similarly to several project equipment items. I need to be able to make RFQ records then for each vendor etc.

These RFQ's then need to be approved etc. so i can then create Purchase Orders from the relevant Approved RFQ's

Any assistance is greatly appreciated.

Thanks,

Sully

[Søren Dyhr]

Alright let me try to get this straight, can one type of equipment say a gadget be supplied from two suppliers, because you wish 5 but supplier can unfortunately only provide 3?

Or do you really issue 3 RFQ's (invite tenders for...) for each set of equipment, where only one of the suppliers wins the order?

This last thing could explain why have put the equipment so far to the left in your graph.

So what Barbara have found actually could be made like this:

http://www.fmforums.com/forum/showtopic.php?tid/193131/post/281056/#281056

--sd

[sullyman]

Hi Soren,

A piece of equipment is always ordered from the one vendor and yes we issue the RFQ's for each set of equipment where only one of the suppliers wins the order.

I tried another way today. Could this work where i have a RFQStatus Field that a user may mark Accepted or Denied etc. and the appropriate price from the accepted RFQ be then transfered to a price field on the equipment table perhaps and then the user could then create a PO seperately and call the equipment from a dropdown with the related price field perhaps?

Or do you really issue 3 RFQ's (invite tenders for...) for each set of equipment, where only one of the suppliers wins the order?

SullyFM.zip

[Søren Dyhr]

I've taken a look at you joins, and there is a problem the 3 join seems redundant Since quantity and price just gets written in the join record when they're available. The same goes with the foreign key for supplier...

(...made a tiny error in the lowest should obviously link to the pk_Order)

--sd

Edited February 13, 2008 by Guest

[Søren Dyhr]

Then is the next question if you have a finite number of equipment to put on each project and they always are the same ... only the suppliers change, In short - I still can't really see why the equipment is squeezed in between Project and the join.

Shouldn't it be equipment assigned to the project, and not equipment in a broad sense?? Or do you really invent new equipment for each project, that never occures in other projects - not even as type?

Perhaps is it because the POV is wrong... if we take an anchor bouy'ish view at the matters is the anchor the equipment and not the project as such, take a look at the above it's an adaption of Bruce's method from the other thread of your wich BTW is very close to doubleposting.... if not???

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

--sd

[sullyman]

Hi Soren,

The equipment is new and different for each project

Sully

Edited February 13, 2008 by Guest

[Søren Dyhr]

But is it of "Type" and specification?

Where have equipment come to the company's attention, isn't there a (some) list(s) of equipment obtainable from the various suppliers, or are you asking both bakers and blacksmiths if they can bid in on the supply of a certain piece of equipment.

The point I try to raise here is that equpiment is a joinfile as well, between a storage and the project.

--sd

[sullyman]

The Equipment is being taken from a SQL Table. I understand what you mean by type but for every project a piece of equipment always has a unique tag number.

I think it is easier to just pick the equipment from the SQL table by import into a local table and lookup. Pity FM doesn't do direct lookups from SQL Tables.

I may have found away to place a column in my table and mark RFQ's approved.

Then i can run a script before PO layout opens to view approved RFQ's. Do you think this will work?

Thanks