ruthcarlton Posted March 2, 2004 Share Posted March 2, 2004 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 More sharing options...
CyborgSam Posted March 2, 2004 Share Posted March 2, 2004 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 More sharing options...
ruthcarlton Posted March 2, 2004 Author Share Posted March 2, 2004 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 More sharing options...
Ugo DI LUCA Posted March 2, 2004 Share Posted March 2, 2004 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 More sharing options...
CyborgSam Posted March 4, 2004 Share Posted March 4, 2004 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 More sharing options...
ruthcarlton Posted March 10, 2004 Author Share Posted March 10, 2004 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 More sharing options...
Ugo DI LUCA Posted March 10, 2004 Share Posted March 10, 2004 So... What is your wish ? I'm not sure I understand what you want to get here ? Link to comment Share on other sites More sharing options...
ruthcarlton Posted March 10, 2004 Author Share Posted March 10, 2004 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 More sharing options...
Ugo DI LUCA Posted March 11, 2004 Share Posted March 11, 2004 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 More sharing options...
ruthcarlton Posted March 11, 2004 Author Share Posted March 11, 2004 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 More sharing options...
ruthcarlton Posted March 11, 2004 Author Share Posted March 11, 2004 Thought more about the portals...using 1 line portals would be a better solution for me. That would solve my hardwiring issues. Thanks for taking the time. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7352 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 accountSign in
Already have an account? Sign in here.
Sign In Now