Search code examples
filterduplicatesfilemakerportalself-join

Filemaker self join portal filtering

I have a database of items that are imported from a number of XML sources, into a table called Import.

Each XML source has its own SourceID number. This number is put in every item record.

Each import is logged in a related table called Import_History. This logs the SourceID, date of each import, and how many items were imported.

So...

TABLE: IMPORT

SourceID = Number

TABLE: IMPORT_HISTORY

fk_SourceID = Number

ImportDate = Timestamp

ImportCount = Number

RELATIONSHIP

Import::SourceID = Import_History::fk_SourceID

WHAT I NEED

On the Import table layout, I want a portal that shows records from Import_History so I can see when each XML source was last imported. I only want to show the most recent Import_History record for each SourceID.

I think I need a cartesian join between Import and a new table occurence of Import_History (Import_History_All), and a self join of a new table occurence of Import_History (Import_History_All_Self_Join).

So...

Cartesian

Import::SourceID x Import_History_All::fk_SourceID

Self Join

Import_History_All::fk_SourceID = Import_History_All_Self_Join::fk_SourceID

WHAT I'VE DONE SO FAR

I have followed this:

http://www.filemakertoday.com/com/archive/index.php?t-32667.html

and this:

http://scarpettagroup.com/filemaker-portal-show-distinct-value-tutorial/

But it does not filter the records. I always get all of the Import_History records in the portal. I have endlessly googled and not figured this out.

Can anyone help?

**EXAMPLE DATA**

*Import*

 - SourceID: 1234
 - Item: Widget1
 - Price: 3

 - SourceID: 1234
 - Item: Widget2
 - Price: 5

 - SourceID: 1234
 - Item: Widget3
 - Price: 10

*Import_History*

 - fk_SourceID: 1234
 - ImportDate: 22/03/2016 12:00:00
 - ImportCount: 3

 - fk_SourceID: 1234
 - ImportDate: 21/03/2016 12:00:00
 - ImportCount: 2
Solution

  • Import_History_All::fk_SourceID = Import_History_All_Self_Join::fk_SourceID

    The relationship above is a join that will result in every record with the specified source ID being displayed in the portal. Basically the relationship above will display the designated portal records in the IMPORT HISTORY table that have the same SourceID. If you wanted to view the most recent record for the sourceID of the record you are on this would be the relationship that is required, and all that would be left is sorting the portal and filtering to only show the most recent value. This would be fine, if you wanted only the most recent importation for the SourceID you are currently on.

    On the Import table layout, I want a portal that shows records from Import_History so I can see when each XML source was last imported. I only want to show the most recent Import_History record for each SourceID.

    The most efficient way to display only the most recent record of each SourceID would be to utilize another table to store the most recent values. This table would contain records with unique values for Source ID ( each source has only one record ) that would contain the Import ID of the most recent record. The import ID is a primary key for the Import_History table that you would add to define a distinction between each Import_History record. Modification of your current importation script is necessary to achieve this result. The modifications should include a variable that captures the Import_ID. The relationship between the tables Import and Import_Tracking will only bring up one record, and then you can use the set field script step to set the field Import_Tracking::Most_Recent to the previously defined variable that holds the value of Import_ID. At this point you have the Import_Tracking table, which contains the unique Import_ID of the most recently updated record for each source. You can use a Cartesian join in the Import table with the Import_Tracking table to show all the records in the Import_Tracking table, which only has the most recent importID for each unique source. If you want to see data from the Import_History table all that you need to do is create a relationship between the Import_Tracking and Import_History table, and then you can place related fields inside of your portal to view the data. I have included the relationships and fields that should be added below. Please let me know if anything doesn't make sense.

    RELATIONSHIP:

    Import::SourceID = Import_Tracking::fk_SourceID (w/ ability to add records via relationship)

    Import_Tracking::Most_Recent = Import_History::ImportID

    TABLE: IMPORT_HISTORY

    fk_SourceID = Number

    ImportDate = Timestamp

    ImportCount = Number

    ImportID = Text/Number (Primary Key)

    TABLE: IMPORT_TRACKING

    fk_SourceID = Number

    Most_Recent = Text/Number (Whatever you defined "ImportID" as)