Professional Web Applications Themes

Relations in portals - FileMaker

Hello, We're facing a problem working with portals. Here's an example: We have a "Company" and "Employee" table. To keep an archive of the companies of an employee, we have an intermediate table that has IdCompany, IdEmployee and DateEnd fields. When "DateEnd" is empty, we know that it's the active company for this employee. What we want to do is to display a list of all employees for a company. So that in the Company table, we have a portal related with the intermediate table. The relation is done with calculation fields, looking for matches of the company id, and ...

  1. #1

    Default Relations in portals

    Hello,

    We're facing a problem working with portals. Here's an example:

    We have a "Company" and "Employee" table. To keep an archive of the
    companies of an employee, we have an intermediate table that has
    IdCompany, IdEmployee and DateEnd fields. When "DateEnd" is empty, we
    know that it's the active company for this employee.

    What we want to do is to display a list of all employees for a
    company. So that in the Company table, we have a portal related with
    the intermediate table. The relation is done with calculation fields,
    looking for matches of the company id, and to see if it's the active
    company for the employee. This works fine, a list of all employee ids
    is displayed in the portal. The problem is that we don't want to
    display ids, but personal information like the name, etc... This
    information is stored in the Employee table. Ideally, we could make a
    sub-relation in the portal with the Employee table using the employee
    ids. FileMaker doesn't seem to support it.

    We found a work around by modifying the database structure. So that in
    the Employee table, we made a calculation field retrieving the id of
    the active company. Using this, a direct relation between the Employee
    and Company is possible. However, FileMaker won't index the
    calculation field, so the relation doesn't work. Instead of a
    calculation, we had to store this value and update it when needed. A
    lookup could avoid the update maintenance in that case. But still, the
    value is stored and we want to avoid it.

    1. So, is there a way to make a "sub-relation" in a portal, like the
    example below? If not, is there another solution?

    2. Concerning the fact that indexing a calculation is not allowed. Is
    there a way to "force" it, or at least in our case, avoid storing the
    active company id in the Employee table to make the relation? Thanks
    for any help!

    Matt
    Rans Guest

  2. #2

    Default Re: Relations in portals


    "Rans" <com> wrote in message
    news:google.com... 


    Hello,

    One idea is to add fields for names, etc. in the intermediate table. These
    can be either calculations or lookups (based on a reverse relationship to
    the Employee table) Since these fields now exits in the intermediate table,
    they can be shown in the portal.

    I'm not sure to understand what you mean by sub-relation, but you could also
    write a script in the Company table to set a global field to the value of
    the current related record's ID, and define a new relationship between that
    global field and the Employee table. Attaching this script to one or all the
    fields in the portal, you could then select a row and have the details
    displayed elsewhere on your layout (a bit like the "preview pane" works in
    Outlook Express) once you select a message header.

    HTH

    Marc-André Paiement



    Marc-André Guest

  3. #3

    Default Re: Relations in portals

    Rans <com> wrote:
     

    Rans,
    The easiest way to display the list of employees would be to define a
    calculation field in your intermediate table,
    IdActiveCompany = Case(Isempty(DateEnd), IdCompany).
    This calculation result can be stored and indexed and used as a foreign
    key in a relationship because it only depends on fields in the same
    record. (An index is a pointer to a record where a certain value occurs
    in a field).
    The employee's name could be stored in the intermediate table when the
    intermediate record is created, either by lookup or by Set field, or it
    could be retrieved dynamically through a relationship from the
    intermediate table to the "Employee" table on IdEmployee. Define
    EmployeeName = IdEmployeeRelationship::Name. This will not be stored,
    but it will retrieve the name for you when you use it from the "Company"
    table.
    --
    Hans Rijnbout
    Utrecht, Netherlands
    Hans Guest

Similar Threads

  1. Perl Beginners Portals
    By Shlomi Fish in forum PERL Beginners
    Replies: 4
    Last Post: October 2nd, 05:13 PM
  2. Not displaying the ID value in popuplists in Portals
    By Michael Bystroem in forum FileMaker
    Replies: 2
    Last Post: September 16th, 09:57 PM
  3. Portals in FM6 on Mac
    By Djuro in forum FileMaker
    Replies: 1
    Last Post: September 16th, 07:57 AM
  4. Help with: Runtimes and portals
    By Basic2 in forum FileMaker
    Replies: 0
    Last Post: August 29th, 10:16 PM
  5. PORTALS in DIRECTOR
    By Christoffer Enedahl in forum Macromedia Director 3D
    Replies: 0
    Last Post: July 15th, 11:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139