4 Replies Latest reply on Sep 22, 2010 12:07 AM by Joser

    Relation problem

    Joser

      Title

      Relation problem

      Post

      Hello to you all,

      could sombody please help me make the correct relation between tables.

      I have got a databse with 3 tables

      -  Files = several dossiers where a price has to be given by tenderers

      - Tenderers = Various tenderers  comprising a price for a given dossier (file)
      - missing article = missing items reported by some tenderers for a dossier(file)


      Sometimes a tenderer announces a missing article, so he gives in the part number & price for this article

      In a jointable i calculate the average of all tenderers for a specific item number who found the missing article. (See table 2 table layout join, post 7)

      Aim now is to recalculate the missing article for all tenderers, including those tenderers who didn't have give a missing article.

      I'm strugling for days now, but it will not work
      I managed to get the fields that has to be calculated in the same portal, and yet I can not use them to make the calculation.

      I made a little database to show where my problems is, but i can't get i uploaded to this forum, if someone would be interested to help me i can mail it to him,

      Kind regards,

      Joser

        • 1. Re: Relation problem
          philmodjunk

          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

          Tenderers----<Offers>----Files

          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?

          • 2. Re: Relation problem
            Joser

            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 :

            http://www.4shared.com/file/MAyAhXdm/Testfile.html

             

            kind regards

            Jo

            • 3. Re: Relation problem
              philmodjunk

              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?

              • 4. Re: Relation problem
                Joser

                Thanks Philmodjunk,

                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

                 

                kind regards

                Jo