Jump to content

Relationship Advice


ruthcarlton
 Share

This topic is 6529 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have files for Dates, Contracts and Vendors. What's the best way to relate Dates and Contacts?

Currently, Dates have Contracts and Contracts have Vendors. I have a calculation field that places Vendors in Dates, but it can't be indexed, so I am using a set field script, which of course has to be activated. A lookup would also have to be triggered with a script.

I will need to track, from within the vendor file, much of the data in the Dates file which is not contract related. I have toyed with the idea of eliminating the Contract File and loading up the Dates file, which although it will work in this specific solution does not seem like the right way to do it.

Any advices

Link to comment
Share on other sites

Ruth->

My first assumption is that Contracts-to-Vendors is many-to-many, a contract can have multiple vendors and a vendor can have multiple contracts. Is this correct?

My second assumption is that Dates-to-Contracts is many-to-many. On any date, several contracts may have activity. I'd simplify this by referring to the Dates as Actions. Each Action is associated with a particular contract, and each Action has a particular date it occurs. This makes Actions-to-Contracts many-to-one, much easier to deal with. Just put the date as part of the Action's record.

In this scenario, an Actions relates to a single Contract which relates to many Vendors. As an example, if you have a layout in Actions showing a single Action record (including the date it occured), the Contract info will be in related fields and the Vendors info is displayed in a Portal.

I deliberately left out relating Contracts to Vendors because I'm not sure my assumption is right...

Is this what you're asking?

Sam

Link to comment
Share on other sites

Actions have multiple contracts.

Contracts have one Vendor.

Vendors SHOULD have multiple contracts.

Contracts are displayed in a portal in Actions. The vendors for the contracts are assigned from a value list based on records in the vendor file.

My concern is that Actions have multiple vendors, but I can't create a relationship between Actions and Vendors with out forcing one with a script.

I hope that is more clear.

.

Link to comment
Share on other sites

Ruth,

You may try to link the Vendors to the Actions by the ValueListItems( ) functions.

Each Contracts has a VendorID and an ActionID

There's a relationship from Vendors to Contracts, call it "ContractsByVendorID"

In the Vendor file, create a Value List of the Action IDs, using your relationship "ContractsByVendorID", and call it "ActionsIds"

Create a field cRelatedActions = ValueListItems(Status(CurrentFileName),"ActionsIds") with this exact syntax.

Now, create a relationship from the Vendors.fp5 to the Actions.fp5 using the cRelatedActions at Left Side of your relationship, matching the ActionID field in Actions.fp5. Call this relationship "ActionsByVLI"

Now, set a portal with this relationship, and you should get a list of all those Actions for this selected Vendor.

Please keep in mind that the cRelatedIDs being unstored could be time consuming, depending on your file size.

So better is an indexed text field, or a global with a script

SetField[tRelatedIds, ValueListItems(Status(CurrentFileName),"ActionsIds")]

Of course, you'd use the tRelatedIds in place of the cRelatedIds in your relationship table.

Have a go with the unstored field, then when you're confident with it, try to switch to the text/global method.

HTH

Link to comment
Share on other sites

Ruth->

> Actions have multiple contracts.

> Contracts have one Vendor.

> Vendors SHOULD have multiple contracts.

Can a contract have one or many actions? This will determine how to setup the relationship between Actions & Contracts.

> Contracts are displayed in a portal in Actions. The vendors for the contracts are assigned from a

> value list based on records in the vendor file.

> My concern is that Actions have multiple vendors, but I can't create a relationship between Actions

> and Vendors with out forcing one with a script.

Can you place a calculation field in Contracts that just returns the data you want from Vendors using the relationship in Contracts to Vendors? Place this calculation field on the line in the portal. This scenario works OK for a small # of these calculations. if you need a lot of data, there is a mroe complex method (ask).

Link to comment
Share on other sites

Ugo -

That solution is great. Thank you.

Next Issue....Each Action record has different types of vendors contracts. Each contract contains cost data which, depending on the contract type relates to the Actions file. 9 out of 10 times there are 5 fixed contract types, so I currently have 5 contract type contants in the Actions file. But this seems like a short sighted solution.

Any suggestions?

Thanks.

-Ruth

Version: Developer v6

Platform: Mac OS X Jaguar

Link to comment
Share on other sites

Oh...

Currently, I am "hardwiring" the Actions file to recognize vendor contract types. So I have a "Headliner" & Event_ID field definition, which relates to a field in the Contracts file ContractType & Event_ID. In total I have 5 of these fields to differentiate the contracts.

By using these relationships I can display the contracts from within the Actions file, and although this

works fine for now, but I have obviously made a wrong turn here.

I want to achieve the same results, but without "hardwiring" the system.

-Ruth

Link to comment
Share on other sites

Well, if you want to have them appear in 5 separate portals, then your settings are OK, either using globals or calcs.

If you want to be able to switch from one to another, then substitute your "Headliners" field in the calc with a global, and use a value list to determine which contract type you want to see.

What I'm not sure from your post is if you have 5 calcs in the related file or in the Main file...

Is that it ?

Link to comment
Share on other sites

OK. The 5 calcs are in the Main File.

Looking at my files again, I see where I should be switching to the related file for data entry...that will simplify things.

Not all of the related data is in portals. I also have layouts with related contract data displayed and with entry allowed.

Are there any "rules" about using fields from a related field in the main file. Depending on the data, sometimes I prefer that to portals. I have built in protection for the user creating additional related records accidently. The records either get deleted or written over.

Thanks

Link to comment
Share on other sites

This topic is 6529 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.