1 2 Previous Next 20 Replies Latest reply on Nov 8, 2011 9:57 AM by kilbot

    Question Relationships and portals

    kilbot

      Title

      Question Relationships and portals

      Post

      Okay I have a table that keeps case information

      I have a table for employees info

      I created a join table (joining employees and cases) and have placed a portal on my cases table. The portal show info from the jointable which is showing info from the employees table.

       

      Now the questions

      Did I set this up the right way? It works fine just creates a record for every entry made in the portal. Will this fill the database up quickly or is it a non issue?

      Now I have created a form letter layout that I need the info from the cases table and the info in the portal lines to show. Does this form letter layout need to be from the cases table or the jointable? How can I write a script to print each portal line?

      Thanks for the help

      ScreenHunter_02_Nov._02_15.04.jpg

        • 1. Re: Question Relationships and portals
          philmodjunk

          The basic set up looks exactly right. The join table only stores two values in every record so can't imagine that it would increase your file size very quickly.

          The form letter could go either way. It depends on how you need to include the data from the join table into this form letter.

          • 2. Re: Question Relationships and portals
            kilbot

            How would I write a script to print info from each portal line?

            • 3. Re: Question Relationships and portals
              philmodjunk

              Please give an example of what you want to see on a page. All the portal data on one page or one such "form letter" for each line in the portal?

              • 4. Re: Question Relationships and portals
                kilbot

                Okay example

                I will need the following data from the CASES table:

                date_current

                number_case

                basis

                issue

                location

                 

                I will need from the JT_EMPCOM_CASES table:

                name_full

                (I will need each line in the portal to print separtly)

                each form letter will be addressed to only one complainant

                 

                I will need from the JT_EMPRES_CASES table

                name_full

                (I will need all names shown in this portal to appear on the form letter)

                all names listed as a respondent must show on each form letter separated by a comma i.e. Bill Parker, Kent Jones, Kimmy Wallace

                 

                 

                 

                Please let me know what I can do

                Thanks

                • 5. Re: Question Relationships and portals
                  philmodjunk

                  all names listed as a respondent must show on each form letter separated by a comma i.e. Bill Parker, Kent Jones, Kimmy Wallace

                  in JT_EMPCOM_CASES, define a calcualtion field as:

                  Substitute ( List ( EMP RES::Full Name ) ; ¶ ; ", " )

                  Make sure that you specify JT_EMPCOM_CASES as the "context" for this calculation if you have other occurrences of this table on the Relationships tab.

                  Now you have all the pieces you need in place.

                  Create your form letter on a layout based on JT_EMPCOM_CASES. Your merge fields can refer to CASES, EMP COM plust he above calculation field to get what you need.

                  • 6. Re: Question Relationships and portals
                    kilbot

                    Maybe I'm doning this wrong. How do I specify JT_EMPCOM_CASES as the "context"?

                     

                    Also side not EMP RES is just a duplicate of EMP COM

                    • 7. Re: Question Relationships and portals
                      kilbot

                      sorry it seems to work had a relationship set incorrectly,

                       

                      but now I need to work on creating a script to print for each line( or record) that created in JT_EMPCOM_CASES related to this record?

                      • 8. Re: Question Relationships and portals
                        kilbot

                        Also how would I make a calculation to show all of the complaints that I listed in the portal from JT_EMPCOM_CASES in the form letter that is on a layout bases on JT_EMPCOM_CASES?

                         

                        I tried the substitution calculation above but it did not work, it only showed 1 name not all names from that portal.

                        • 9. Re: Question Relationships and portals
                          philmodjunk

                          How do I specify JT_EMPCOM_CASES as the "context"?

                          In the very top of the Specify Calculation dialog there's a drop down list of all table occurrences that refer to the data source table where you've defined this field. Select JT_EMPCOM_CASES in this drop down.

                          but now I need to work on creating a script to print for each line( or record) that created in JT_EMPCOM_CASES related to this record?

                          Starting from a layout based on Cases, your script can use either Go To Related Records or it can perform a find using the case ID to pull up the records in JT_EMPCOM_Cases (Must be a layout that refers specifically to that name in Layout setup | Show Records from).

                          If you use Go To Related Records, make sure to include a check either for the existance of related records in the join table or for any error messages returned by the Go To Related Records step.

                          If you perform a find instead, copy the current Case ID into a variable, then have your script change layouts and perform the find on the Join table--using the ID in that variable as your find criteria.

                          I tried the substitution calculation above but it did not work, it only showed 1 name not all names from that portal.

                          That suggests a problem with your layout/table "context". If you put a portal to EMP RES on your JT_EMPCOM_CASES based layout, do you see all the expected names listed there?

                          Also side not EMP RES is just a duplicate of EMP COM

                          Have you used a separate data source table for the two join tables or are they occurrences of the same table? (If you hover your mouse over the arrow in top left corner, do you see the same data source table named?) If they are occurrences of the same data source table, we'll need to make some changes in what I'm recommending here...

                          • 10. Re: Question Relationships and portals
                            kilbot

                            No the join tables are created separtly and the EMPCOM and EMPRES are table occurances duplicates

                             

                            also when I do place a portal from EMPRES on the EMPCOM layout I do see the listed employees.

                            However I'm trying to get a list of all of the complainants from the portal on my CASES table on my form letter which is in the EMPCOM table. This means a list of name from the same table I am currently in. Does this make sense?

                            • 11. Re: Question Relationships and portals
                              philmodjunk

                              OK, that's what I've assumed here, so we shouldn't need to modify the approach.

                              • 12. Re: Question Relationships and portals
                                kilbot

                                So how do I get a list of employees that were listed in the portal(JT_EMPCOM_CASES) on the CASE layout to show on the form letter that I have now created in the (JT_EMPCOM_CASES) table. Would it have to get the name from each related record? I trying the substitution calculation which works for gettin a list of respondents from the _JT_EMPRES_CASES table.

                                 

                                See below i can only get one name to show from the 3 that were in the portalfor the complainants

                                 

                                • 13. Re: Question Relationships and portals
                                  philmodjunk

                                  That's exactly what I was understanding that you want.

                                  A portal on the JOIN layout shold list the same names and the List function should return them. Then the substitute function should be able to replace the returns with a comma and a space to get the results you need. That's because Filemaker should be able to "trace back" through the relationships from JT_EMPCOM_Cases to CASES to JT_EMPRES_CASES to EMPLOYEE RES.

                                  What did you see by putting the portal I specifiied on the Join table layout? This is a quick check to see if your basic set up will work for this or not.

                                  • 14. Re: Question Relationships and portals
                                    kilbot

                                    When I place the portal showing JT_EMPCOM_CASES it does not show anything.

                                     

                                    JT_EMPRES_CASES shows all

                                     

                                    But I'm trying to get a list of the names from JT_EMPCOM_CASES while on a layout created in JT_EMPCOM_CASES

                                    1 2 Previous Next