Jump to content

Search the Community

Showing results for tags 'join table'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Community Forums

  • The New FileMaker Platform
  • Community Resources
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro Advanced 18
    • FileMaker Pro Advanced 17
    • Legacy FileMaker Platform Discussions
  • FileMaker Server Administration
    • FileMaker Server 18
    • Zabbix Server Monitoring
    • FileMaker Server 17
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Bar Codes (Printer, Scanners, Software)
    • Hardware & Networking
    • OS Level Database Automation
    • Product Ideas
  • JavaScript Integration
  • FMForums Affiliates & Sponsors
  • FileMaker Classifieds
  • FM Forums Operations
  • FileMaker Friday Night Chat's Topics


There are no results to display.

There are no results to display.


  • White Papers
  • Infographics
  • Samples
  • Solutions
  • FMGo
  • Plug-Ins
  • Tutorials

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

Found 16 results

  1. Hey... Here is the scenario... Contact table joined to Contact|Event table joined to Event table Portal in Contact table of Event table to see all Events linked to a particular Contact Needing to link 100 (or any number) of Contacts to 1 Event in Event table Do not want to do this manually. I have all the tables and relationships set up and everything works. All I need is a way to create multiple records (joint records in Contact|Event table) in multiple Contact records so I don't have to enter them in one by one...which of course I can do. I figure this would be a script...but I don't even know where to call the script...in the Contact record? In the portal row? in the Event record? Where does the script button go? And what is the script? Once I create a found set in Contacts, how do I assign one Event to many contacts (in the found set) without having to enter the link manually. THANKS!!!!! Todd
  2. Hello everyone; i am a bit annoyed that i just dont understand the join table from a tutorial that i have been reading. It is an invoicing solution which is great, but the instructor doesnt really go into depth explaining why certain things are in the invoices table & not in the line items table. Can someone please explain to me why you would have: price - *main data in invoices table ( lookup in lineitems table) qty - only in lineitems table , not in invoices table extended price ( only in line items table) total of invoice - (only on line items table) i am sure someone can explain reasonably simply what the join table(s) purpose is and what data NEEDS to be in that join table. Thank you for sharing with a curious newb as myself. --i
  3. I am very much a novice with FM so any assistance is greatly appreciated. I am using FM 13 to develop a local digital asset manager. I have one portal with a list of keywords connected to a list of assets via a join table. When a keyword (button) is selected, the related ASSETS are displayed in a second portal. On a basic level, this setup works correctly. However, I am trying to expand the ASSETS that are displayed. Currently, the ASSET portal is filtered using: global::gCurrent Keyword = Data for Keywords::ID Given this example 1 doc1.pdf 1 doc2.pdf 2 doc3.pdf 2 doc4.pdf If I enter "1" or "2" in global::gCurrent Keyword, then the expected records are displayed (1= doc1 & doc2, 2= doc3 & doc4). However if I enter a list containing 1¶2 then nothing is displayed. Any suggestions on how this could be resolved would be appreciated. Thanks in advance for your assistance.
  4. I have 3 tables: Person, Session, and Contract where Contract is a join table. I am in the Session table layout, in a Contract portal. I have a field – Trainer Name in the Contract table – where I want to select the name of a person from a list of people in the Person table. The Trainer Name field is a dropdown list using values from the Trainer value list “Person::__k_p_Person and Person::Name_Full”. I have tried numerous configurations with table instances and I either cannot alter the Trainer Name field at all, or I get a “?”. After the Contract record is created, I want it to show up in a Contract portal on the person’s record in the Person table. Each person will teach multiple sessions. Each session has multiple trainers. I have foreign keys in the Contract table for Person and Session.
  5. I thought I had a semi-fair grasp on basic join tables, but....NOPE! I've spent the last two days scouring my copy of FTS, looking at the starter solutions, digging through this forum, and watching YouTube videos on how to use Join Tables and I'm just not able to grasp the concept. I have three TOs: People ---< JoinGroup >--- Events - The JoinGroup Table has it's own primary Key, two foreign Keys (one each from the People and Events tables), some looked-up fields from the people and events tables, and a few Group Table-specific fields. So far, so good. Unfortunately, this is where my brain shuts down. When I add a new record in the join table, I can't figure out how to select which person from the People TO and which Event from the Events TO go into the new record without resorting to manually looking at the main tables, writing down their primary Keys, and manually entering them into my new Join record. Yuck! Is there a way to have a list of the people and events show up on the join table layout so I can pick the name and event? I have list layouts of both other tables but do not how to access them from the join table. I have tried putting portals on the join table layout to show the people and events, but they are blank until I manually enter the foreign keys which makes just the one person and one event show up (which is a good thing for a specific join record, but not to initially find the person/event). Should the foreign Key fields in the join table be set to (Auto Enter) look up the Primary Key field in the People and Events tables or just left empty? I've tried both ways and it doesn't seem to do anything either way, but that is probably because I have some fundamental failure in my setup. Any guidance would be much appreciated. Guy
  6. I am not an experienced user so I may be way off track with this but any help appreciated.  I am building a database to manage Dog Breeding data and I have hit a problem that I am struggling to resolve.  The Database has a “Dogs_Details” table that holds the relevant details specific to each animal (appox. 4000 records spanning 150 years, many historic records are incomplete but that is not a problem). There are various tables linked to the “Dogs_Details” table that hold other relevant data about health, parentage, (i.e Pedigree), registrations, identification etc. The “Dogs_Details” table has a join table with the “Owner_Details” table so Owners and Dogs are linked.  So far so good - it all works.  I am now working on the breeding part of the database so there is a “Season” table to record Bitches receptive periods, a “Mating” table that records matings, a “Whelping” table that records details of the birthing of each litter of pups, a “Litter” table that records the progress of each litter of pups and a “Whelps” table with a record for each puppy's individual progress in the Litter.  Everything seems to work fine although I am sure my relationships are untidy to the expert eye.  However. Once the puppies have reached a point of maturity where they may be homed they need to be added to the “Dogs_Details” table as Dogs because they have become dogs in their own right, so how do I link the whelp to its adult record?  To be precise the part I am struggling with is; How do I get the Whelps (puppies) recorded in the “Whelps” table to become or link to their adult “Dog” record in the “Dogs_Details” table.  I have tried adding a field with the UIN (Unique Identifying Number) of the Whelp to the Dog's record in the "Dogs_Details” table as a FK and I have also  tried a join table with the Dog's Unique Identifying Number and the Whelps Unique Identifying Number linked but whatever I do I finish up with a circular reference that isn’t permitted.  I am at a loss.  The analogy might be with other animals such as (Human’s) where individuals produce offspring who might go on to produce offspring themselves - how is that handled in say a medical database.  It is not practical to add all neonates to the "Dog's Details" table because not all may survive infancy and that would lead to numerous pointless records but I do want to track the progress of say a runty puppy through to adulthood and track such things as longevity and health.  I don’t have a background in Dbs and although the database is now doing most of what I need I can’t fathom out how to resolve this conundrum.  Is it even possible? or do I have to just work around it?  I have attached an image of a relevant section of the ERD in the hope it explains things a little better.  TIA
  7. hello. I am new to relational dbases to apologies to start. I have a dbase (LOAN) that contains loan information. It has a primary key field _kp_LoanID . I have a dbase with people (COMPANY) in it. These people can be lenders, borrowers, escrow folk, etc….. It has a primary key field _kp_CompanyID. Each of the Primary Keys are auto-enter serial fields with required, unique values. I have a join table LoanCompany with it’s own primary key field _kp_LoanCompanyID and two foreign key fields _kf_LoanID and _kf_CompanyID. As the same person often wears multiple hats i thought it best to put the role_in Company field in the join table also. The person and their role is linked here and COMPANY can JUST be the contact information for the person. There is a many to many relationship between these two main dbase, as the same person can have many loans associated with them and one loan will have many people associated with it. I have created a relationship between the LOAN dbase and and its associated foreign key in the join table, and the COMPANY dbase and its associated foreign key in the join table. I have created layouts to enter data in the non-join databases. I can create a new loan record and then go over and enter a new company record. On the company layout I then choose what loan that COMPANY record is associated with and what hat the company person is wearing. I put related fields on the layout to ensure I was getting the correct loan and join information. A new record is created in the Join dbase when a new company record is created-that is the foreign keys are created once both parts of the relationship between LOAN and COMPANY have been established. (FMP automatically creates the matching foreign keys because I put those fields on the Company input layout, I believe. I tried with just the join fields and with them thru a portal. It did not seem to have any impact one or the other so I took the portal out.) What I want to do is find a company record that has been used previously on a loan ( has at least one join record in the join table) and create a new instance in the join table so I can add a different role to that person with the same loan or possibly just add them to a new loan, with the same role or maybe add them to a new loan with a new role. The problem is I cannot get a new join record created that I can then fill with appropriate Loan and Company and Role information. Without that when I have a company record up and modify the _kf_LoanID to get a new loan associated, it just CHANGES the join record. The same occurs with and modification of the role_in_Company field. The Join record is MODIFIED, but not a new one created. Being able to modify it is helpful. But some how I need to be able to independently add a new record to join to make the dbases useful. Or have someone tell me I really mucked up the design of the two databases in the first place and a better way to lay that out. Thank you in advance for your assistance, scott
  8. Hi, I am new here and quit new in FM world but have a silly question regarding join tables for more than two tables. I have created three table join Table 1: id1 text Table 2: id2 text Table 3: id3 text Join table: id id1 id2 id2 I built a Layout for first table having two portals on it, showing records from table 2 and table 3. The relation is through a join table. Creating a first portal record in both portals creates only one record in join table that refers to both tables (2 and 3), a three way join. For all following portal records FM creates separate join records between table 1 and 2 as well as table 1 and 3. Does anyone know why it is so and how to overcome it? Thank you in advance
  9. Is there a difference in how Filemaker reads/acts on the two following Relationships: 1. Parent Table A --< Join Table AB >-- Parent Table B and 2. Parent Table B --< Join Table BA >-- Parent Table A ? After my last disastrous foray into designing a Relationship Graph, I went back to the Filemaker Training Series to re-re-re-read about building correct ERDs and Relationship Graphs, but only managed to confuse myself even more. The FTS examples all use the Anchor and Buoy concept, but seem to have a huge amount of unnecessary repetition, unless I'm completely off base. Using their "04_Bonsai" Relationship Graph as an example, they have one Anchor-Buoy set of ORDER --< order_LINEITEM >-- order_lineitem_PRODUCT and another Anchor-Buoy set of PRODUCT --< product_LINEITEM >-- product_lineitem_ORDER. These seem functionally IDENTICAL to me - you can even swap the icons around so the ORDER TO is on the left, the LINEITEM TO is in the middle, and the PRODUCT TO is on the right for both sets without changing the Relationships between them (and yet a third set with LINEITEM on the left and both lineitem_ORDER and lineitem_PRODUCT to it's right, which can be manipulated to match the above two sets as well). If i'm sitting on a record in the ORDER TO, looking through the LINEITEM TO into the PRODUCT TO, does it matter if I have to look left or have to look right? Or are they just adding a bunch of Anchor-Buoy sets on the Graph to, ummmm, "clarify" process flows? Are there any performance issues in eliminating the redundant TOs? Is it considered a Best Practice to place a TO on the left of the Graph for every Table and delineate Relationships to the right of each beginning TO?04_Bonsai Relationship Graph.pdf I have included a screenshot of the Relationship Graph (squished to fit) to illustrate my problem and, as always, your insights are greatly appreciated. Sincerely, Guy
  10. I am working on a scheduling module for our production shop. I want to display the Work Orders in a list and Schedule time to each of the Stations between the Work Order IssueDate and ShipDate. Secondly, I would like to report back the actual hours spent vs. the Scheduled time for progress reporting. Tables: Work Order (500/year) WorkOrder# Description IssueDate ShipDate Stations (7-10) StationID StationName Schedule (1 for each Work Order) WorkOrder# StationID TimeEstimated TimeTracking (1000s/year) EmployeeID EmployeeName WorkOrder# DateWorked TimeWorked Employees (20+) EmployeeID EmployeeName Thank you for your guidance. Mark
  11. Hi everyone. I’m trying to accomplish a conditional value list and I’ve been working with a few sample files I have (I still don’t get the whole picture of how it works, I always miss something), and I’m stuck (once again, lol). The idea is: Meeting Holdings Companies Executives Is going to take place a meeting where some executives may go. That depends on what Holdings (not single companies) are going to be represented in the meeting. I need to condition the Executive value list to the Holdings they are in. I attach my sample file. Thanks in advance. Conditional VL Join Tables.zip
  12. I’m attaching a cluster of screenshots that document a join table. All field are indexed. It looks correct to me, but I’m not able to enter groups in the portal. I can’t even access the field. Any help would be appreciated. Thanks. Join.zip
  13. I have viewed many tutorials and asked many developers to help me get to what I have already. What I have not asked is the following question: How do I populate my portal? Ok, here's what I have. An Attendance Solution that has three tables: NAME: _kp_name_id name enable DATE: _kp_date_id _kf_name_id _kf_status_id date STATUS: _kp_status_id status They are joined by the obvious keys. Lets say that the NAME table has four records: Bill Enable Mary Enable Steve Laura Enable What I am trying to do is get all of the names that are enabled in the NAME table to appear EVERY time in a portal under the DATE table. (Is this my first issue? Which layout view and table should my main and portal be based upon?) AND I also want the field status, from the STATUS table, to appear next to their names (as a radio-buttoned value list). AND I want this to be the case EVERY time I create a new Date record. So for example, I want the following: Record 1: DATE: 12/24/2012 Bill Present Sick Work Travelling School Other Mary Present Sick Work Travelling School Other Laura Present Sick Work Travelling School Other Record 2: DATE: 12/25/2012 Bill Present Sick Work Travelling School Other Mary Present Sick Work Travelling School Other Laura Present Sick Work Travelling School Other Record 3: DATE: 12/26/2012 Bill Present Sick Work Travelling School Other Mary Present Sick Work Travelling School Other Laura Present Sick Work Travelling School Other I want this to appear EVERY time. So, I ask, is this a filtered portal issue? A scripting issue? Will someone please be kind enough to help me through this?
  14. I am trying to create a payment solution for my existing database. I have everything else working right now, but no way to keep track of payments. As it appears from my searching on Google, I need to create a join table as I have a many to many relationship. So this is what I have now. Invoices:ID_Invoice ---<Invoice_Payments:id_invoice Invoice_Payments:id_payment>---Payments:ID_Payment Inside my join table, I have a field for Payment_Portion. This way I can designate how much of the payment will go to each invoice. Sometimes I get a payment for one invoice. Sometimes its for a couple. Sometimes its like a down-payment or something on that order. On my Payments layout, I have a field to look up the Account and pull that info from the Accounts table. That works good. I created a portal to the join table (Invoice_Payments) and added fields from the Invoices table to the portal. My problem is that I can not get anything to show up there. What I am trying to accomplish is that ANY invoice for the account above that has a balance more than zero will show up. This way I can select which invoice to apply the payment to and how much gets applied to each invoice. I have seen this done in the Core3CRM database, but since I am not looking to purchase new software at this point, I figured I could create the same thing in my database. But it has proven a little challenging right now. I am sure this is something very simple to do, but it is above my knowledge level. Any help or guidance will be greatly appreciated. Let me know if more information is required to help.
  15. Hello, I'm a relative newbie to databases and Filemaker - so please excuse me if there is a really easy answer to this that I am not aware of. So... here is my scenario: Purpose of database: to link together people, organisations, evidence (i.e., pieces of information relating to people, organisations, or the relationships between people-org, people-people, org-org). I won't bring all the tables into this discussion as one the problem has been solved in one area, it is solved everywhere. So, taking this particular instance: My tables are: People: __pkPersonID, title, firstName, surname, AKA, fullName, DOB, DOD, information, notes Organisation: __pkOrgID, orgName, founded, terminated, notes JOIN_people_organisation: __pkJOINtableID, _fkPersonID, _fkOrgID, relationshipDescription, notes Note - the JOIN table is there because there will be many-to-many relationships My situation is this. I will have a large number of people, a large number of organisations and the need to continually add new people and new organisations - plus the ability to add either/or during the creation of a new relationship between them. Here's some fake data (not relevant to my database) People: 1, John Lennon 2, Paul McCartney 3, Ringo Starr 4, Mick Jagger 5, Kurt Cobain 6, Courtney Love 7, Alan Klein 8, Bill Geffen Organisations 1, The Beatles 2, The Rolling Stones I create a layout based on the JOIN table with the aim of creating relationships. This is where my problem starts. I add my first relationship, an easy one - John Lennon, The Beatles PROBLEM 1: how do I remember the _fkPersonID that relates to John Lennon without leaving the layout and noting it down? Is there a way of creating a live 'search' field within the layout that lets me search for a person to see if (a) they already exist and ( if they do, to then populate the relevant fields. PROBLEM 2: I'm looking at this from the reverse. I decide to add George Harrison to my database. So, I assume an easy way to do this would be to go to the JOIN table layout, search for The Beatles, populate one field with the relevant _fkOrgID (associated with The Beatles) and then add, presumably in another set of related fields or a portal, George Harrison as a new record in the People table that relates to this? PROBLEM3: For example. Say I was looking at a new type of relationship. Still an organisation, but say this was 'association of 60's legendary musicians'. So, I add this to the Organisation table. I then go to the JOIN layout and 'search' for this, populate the field with the relevant foreign key. Then I want to start adding people to this relationship. ... search for Paul McCartney, add him as a relationship ... search for Mick Jagger, add him.. ... search for Dave Crosby.. he's not there, so ADD Dave Crosby ... ADD Bob Dylan ... search for Ringo Starr, add him. Etc. You can see why I am looking for this solution as it makes the data entry side of things much more useful. Especially if other people add to the database to build more records and more relationships. Any help would be gratefully appreciated. I've tried all sorts of key words to find an answer on google. Looked through Lynda.com, missing manuals etc. Thanks in advance
  16. I am building a digital asset manager in FM 13 for Windows. I am having problems displaying filtered data in a portal. I have series of scripts that bulk imports the file names & folder structure and creates a collapsible hierarchical keyword structure that is comparable to the folder structure. It creates all the joins between keywords and assets. Example: C:\Automobiles\Cars\Fiat\Model1.pdf results in C: ---Automobiles -----Cars ---------Fiat When "Fiat" is clicked, all assets associated with this path are shown in a second "Asset" portal using the filter: not IsEmpty ( FilterValues ( Data for Keywords::ID ; global::gCurrent Keyword ) ) This portion works fine. User added keywords are intended to appear in the same keyword portal using the structure of: c: (imported example above) Author ---A ---B etc Series ---A ---B .... ---F ------User Added keyword (ie Fiat) A script inserts the user created keyword alphabetically into the keyword hierarchy and creates the join table record. This is where the problem occurs. Clicking on the user keyword in the keyword portal returns an empty asset portal. I have double checked all PK values in the asset and keyword tables against the matching join table records and they are correct. I have also manually deleted and inserted working and no-working join link records to see if there is an issue with how the joins are created (manually vs script) without affect. Any suggestions on why this may be occurring would be greatly appreciated. Thank you
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.