6 Replies Latest reply on Jun 2, 2016 3:52 PM by Michiel

    Multiple calculations from related table

    Michiel

      Dear all,

       

      Englisch isn't my native language, so please forgive me. I hope you can help me. This is my first FM solution, so I'm really a beginner.

      My database has (among others) the following tables:

      Contacts      and      Documents

      Contact_ID               Document_ID

      etc.                           fID_Contact_ID

                                       Document_Type

       

      Every document belongs to only one contact. A contact have more documents. Clearly a one-to-many relation (Contacts:Contact_ID=Documents:fID_Contact_ID).

      In my "contacts-layout" I have made a portal that shows me all the documents related to that contact. Works fine and so far so good.

       

      Let's say one of the document type is "Type x". I want to create a calculation field in de table 'Contacts' that will calculate there is a 'Type x' document for that contact. I created the following calculation:

      If ( Documents::Document_Type="Type x" ; "Yes" ; "No" )

       

      This works, but only for the first related record; Only if the first document of this contact is 'Type x', the calculation will show 'Yes'.

      After reading several discussions I changed 'sorting records' in the relation between the two tables, by sorting on 'Document_Type'. This solves the problem, but only for 'Type x'. I also want to make a similar calculation for 'Type y'. The result is 'No' when the first related record is 'Type x' and the second related record is 'Type y'. That's incorrect, because there is a document 'Type y' for that contact.

      So my question; how do I solve this?

       

      Extra info; why these calculations?

      I want to create a list of all contacts, where I can see quickly if the contact has an document 'Type x' and/or 'Type y'. Any suggestions are very welcome!

       

      Thank you in advance.

        • 1. Re: Multiple calculations from related table
          Extensitech

          I believe the challenge you're having is that you want to check the "list" of documents. If you don't tell FM that you're talking about the whole list, one record is assumed, so it just gets the first one.

           

          Try this:

           

          Case (

            PatternCount ( ¶ & List ( Documents::Document_Type ) & ¶ ; "¶type x¶" )

            ; "Yes"

            ; "No"

          )

           

          The pilcrows (¶) are there to be sure we're checking whole lines, not just partials, so if type x is really "big" and type y is really "bigger", "big" won't find "bigger".

           

          Extra info; why these calculations?

          I want to create a list of all contacts, where I can see quickly if the contact has an document 'Type x' and/or 'Type y'.

           

          If you just want to find records with "type x" in a related document type, though, you can search for that. The Find does look at all related records. So, if you put Documents::Document_Type on your Contacts layout (with or without a portal) and you search for where Documents::Document_Type is "==type a", you'll find all contacts with that type of document, even if it isn't the first in the list.

           

          Hope this helps.

           

          And by the way, your English seems just fine, and certainly easily understood. Where are you from?

           

          Chris Cain

          Extensitech

          1 of 1 people found this helpful
          • 2. Re: Multiple calculations from related table
            Michiel

            Dear Chris,

             

            Thank you very much for your help.

            Your suggested calculation works just fine!

             

            I made a Lay-out with the option 'list' and a simple script:

            1=Go to Lay-out 'List'

            2=Search (Documents::Document_Type==Type y)

             

            It shows me all contacts with an 'Type y' document. Works just fine.

            However I wanted a list with all contacts (also the contacts without Type y document) where I can see Type x=Yes or No and Type Y=Yes or No. Therefore the calculation field. If I take these calculation fields into the list lay-out, I can see by all contacts 'Yes' or 'No' for each type of document. Do you agree?

             

            Thanks again for you help! BTW; I'm form The Netherlands.

            • 3. Re: Multiple calculations from related table
              Extensitech

              Michiel wrote:

              Therefore the calculation field. If I take these calculation fields into the list lay-out, I can see by all contacts 'Yes' or 'No' for each type of document. Do you agree?

              Yes.

              • 4. Re: Multiple calculations from related table
                Michiel

                Dear Chris,

                 

                I have some challenge regarding my initial post. I hope you (or one of the others) can help me with it.

                 

                My database has now (among others) the following tables:

                Contacts      and      Documents

                Contact_ID               Document_ID

                Calc. Type x             fID_Contact_ID

                name etc                  Document_Type (F.E. type X)

                                                Exp. date (f.e. 05-20-2016)

                                                Calc. status (active/inactive)

                 

                For the field 'Contacs:Calc. Type x' I used your previous solution. Works just fine.

                Also the calculation for the status of the document works just fine.

                 

                Now I want to create a list of all contacts, showing me not only 'Contacts:Calc. Type X' (Yes or No), but also the status (active/inactive) and exp. date (05-20-2016) of the document with type X.

                 

                I need to create the list by the table 'Contacts', otherwise the list can't show me all the contacts, but only the contacts with an Type X document.

                Other type of documents however also have an expiration date, so the question is how to manage showing the exp. date of that specific (related) document with Type X.

                 

                I hope you can understand my question and look forward to your answer.

                Thanks in advance.

                • 5. Re: Multiple calculations from related table
                  Extensitech

                  If (big if ) I understand you correctly...

                   

                  You could accomplish this by making a second relationship between Contacts and Documents.

                   

                  Create a field in Contacts that is calculated to always equal "X".

                   

                  Create a relationship from Contacts to DocumentX, where Contact_ID = Document_ID, and your calculated field equals Type. Via that relationship, you'll only see documents that belong to this contact and are also of type "X". You can then calculate the expiration date of the X document as =DocumentX::ExpDate.

                   

                  If you want to only include active documents, you can follow the same pattern, with a field in contacts calculated to equal "active". If there's a possibility of more than one, you will want to sort the relationship to DocumentX such that the one you want will end up at the "top" of the list, i.e. sort by exp date descending (?).

                   

                  HTH

                  Chris Cain

                  Extensitech

                  1 of 1 people found this helpful
                  • 6. Re: Multiple calculations from related table
                    Michiel

                    Thank you Chris,

                     

                    Your solution works!

                    Now I can see a list with all contacts, the result of the calculation document type X =yes or no, the exp. date of document type x and the status of document type x.

                    Superb, that's exactly what I wanted!

                     

                    Thank you so much for you help.