You can upload your file to a file sharing site and then post the down load link for it here.
You can also capture a screen shot of your relationship graph and edit your initial post above to include it.
I can't tell from your post above whether "Tenderer" represents a person/company or a specific offer made by that person/company.
If they are individuals, not offers (tenders), then I think you would need this relationship
Missing article: do you have one "missing article" record for each tenderer that reports one or just one record for each article that is reported?
Hello Mr. PhilModJunk,
it seems that i didn't translate it correct.
I 'll try to explain , the database is for calculating offers of buildingmaterials
We give contracters a dossier(files) who excist of different articles (plumbing, concrete, wooden beams,sanitair....)
They give us their price per article.
It could be that i forgot to put an article in the dossier(file) for example : masonry
when they bring in their offer, they tell us (on a note) we forgot an article and they give us their price for that.
It's my job to compare the offers of the different contracters
Therefor i have to calculate the average of the price of the forgotten article and recalculate it comparing their totalprice of their offer
short version with numbers ;
offer of contracter 1 : 11500
offer of contracter 2 : 12500
offer of contracter 3 : 15000
contracter 1 mentions i forgot article 123 for the amount of 500
contracter 3 also mentions i forgot article 123 for the amount of 700
now i have to calculate the average of the offers = 13000
the average of the forgotten article : 600
But now (here is my problem) i have to recalculate the offer of all the contracters (also contracter 2)
contracter 1 : 11500*600/13000 = 530,76
contracter 2 : 12500*600/13000 = 576,92
contracter 2 : 15000*600/13000 = 692,30
And that is where i struggle.
As you suggested i've put the file in a file sharing site :
Lack of a common language is making this more of a challenge.
Your table structure is a bit odd.
Id have an additional table: Articles
Files (I'd call them projects)---<Articles
Where each article that is needed for a given project is listed and are linked by the dossierID (I'd call it projectID in English).
Each contractor, would be linked to a Project by a join table, Project Bids since many contractors can bid on many projects. Their BidItems table would also link to this join table. A script would duplicate the records of a given project's Article records in this BidItems table so that they can enter individual amounts. You'd need a script to go to each contractor that missed the missing item, add a new bid article record and give it the average amount you describe. You can set up a relationship that links a Bid Article record to all other Bid_article records with the same ArticleID and ProjectID. The aggregate function Average(Same_BidArticle:Amount) would then compute the desired average.
This last question has nothing to do with FileMaker, but wouldn't you want to contact the contractors that didn't identify a missing article and ask them to update their bids? Doesn't the fact that they missed this detail indicate that they might not be a good choice for doing the work anyway?
i' ll try it adding an extra table for the articles,
I suppose i will have to let this script run after all missing articles are put in, and do a search which contarctor didn't bid for a specific article....
About your question ; this is just the beginning (step 7 of 10) of a system for recalculation obligated by our goverment.
in this stadium it's just a recalculation to be able to compare all contracters with the same amount of articles.
It doesn't seem fair for those who did found a missing article, but at the end of recaclulation (step 9 annd 10) they are sort of 'rewarded'.
So normally they who didn't find a missing article become last at the end of the complete recalculation