Search the Community

Showing results for tags 'relationship'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Custom Function Library


  • Journal

Community Forums

  • Community Resources
    • FileMaker Pro 16 Discussions
    • FileMaker Pro 15 Discussions
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Security Management
    • Security Concepts
    • Intellectual Property
  • FileMaker Server Administration
    • FileMaker Server 16
    • FileMaker Server 15
    • FileMaker Custom SSL Certificates
    • External Server Authentication
    • Stand by Server Deployment
    • Legacy FileMaker Server Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPhone & iPad
    • iBeacon Support
    • FileMaker IOS App SDK
    • FileMaker Discussions
    • Brain Food
  • FileMaker and the Internet
    • FileMaker Cloud
    • FileMaker WebDirect
    • Custom Web Publishing
    • Other Internet Technologies
  • Geist Interactive Product Support Forums
    • Visit Geist Interactive
    • Visit Modular FileMaker
    • FMPerception
    • Generator
    • fmQBO
  • 360 Works Official Product Support Forums
    • 360 Works General Support
    • MirrorSync by 360Works
    • SuperContainer by 360 Works
    • ScriptMaster by 360 Works
    • FTPeek by 360 Works
    • 360Works Email Plugin
    • DocuBin by 360 Works
    • Zulu – FileMaker, iCal & Google Calendar.
  • FM Forums Affiliate Sponsors
    • SyncServer Pro by LinearBlue
    • Open Source Frameworks
    • Monkey Bread Software (MBS Plugin)
    • FileMaker Plug-Ins
    • ISO FileMaker Magazine
    • User Group Central - Sponsored by
  • FM Starting Point - By Richard Carlton Consulting
    • Visit FM Starting Point
    • FM Starting Point - General Discussions
  • FileMaker Classifieds
    • FileMaker Product & Service Announcements
    • Professional FileMaker Training
    • Services for Hire
    • Services Wanted
    • Solutions Wanted
    • Tools Of The Trade
  • The Water Cooler
    • Member Lounge
    • Wants & Wishes
  • FM Forums Operations
    • FM Forums Feedback & Site News
    • Site Instructions


  • Captain's Blog
  • FileMaker Weetbicks
  • FileMaker Security Blog
  • The FileMaker Rift
  • HOnza's Bits @ FMForums
  • SeedCode
  • CampSoftware, Hal Gumbert
  • FileMaker Hacks
  • Skeleton Key's Blog
  • Wing Forward Solutions
  • 360Works Articles and Tutorials
  • GoBillit
  • Filemaker - an amateur's view
  • Linear Blue
  • Surya Kanta Mekap's Blog
  • eXcelisys' Blog
  • Manjit Behera
  • Blue Feather's Blog
  • RCC's FileMaker News and Bits
  • Thorsen Consulting
  • DB Services Blog
  • FileMaker Magazine
  • Eye on FileMaker
  • Dev Talk
  • The Philosophy of FileMaker


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

Found 51 results

  1. Hi, I have a configuration table (named 'Usager actif') with 'Projet ID actif' and start/end date values (field name 'Bilan date début' and 'Bilan date fin') linked to a timesheet table (named 'FTemps lié Projet bilan') to show only the records that match, according to the following relation: This table is also linked to a step table (named 'Étapes lié FTemps lié Projet bilan') to show a list of steps used by the timesheet table (named 'FTemps lié Projet bilan'), base on the following relation: So the last table (named 'Étapes lié FTemps lié Projet bilan') is shown in a portal and it's working correctly (show only two steps used, for all the timesheet records). But I wanna show the total time used for each step (first step is 5 hours, and last one 95 hours). It's working correctly if the start/end date included all timesheet records. But if I shrink the ending date (removing 2 hours from the timesheet in step 2), the total time by step still show all time since the context does not follow from timesheet table to the step table in relation. I still see 5 hours for step 1 and 95 hours for step 2 (should be 93). The total time for all step is now 98 hours, since the ending date has been shrink (removing 2 hours on step 2). How can I do this (hope fully I'm clear enough)?
  2. I am currently building a database to handle the inspection sheets used by our Quality lab. Here is the relationship layout: For each revision on the Revisions table, I have a differing number of Dimensions that need to be measured (some parts/revs will have 5 dimensions, while others will have 15). Each dimension has a specified type of instrument used in measurement, and a max/min value. The instrument used is a drop down menu that references a list of instruments/gages that we use. Here is how I have my inspection sheet currently laid out: The portal on the left points towards my dimensions table. It shows the data corresponded to the specified Part Number and Revision level. The data contained in this portal is uneditable from this form. The portal on the right points towards my RecordedDimensions table. It will store data that will correspond back to the Lot# associated with that inspection sheet. I also have a table that pairs all the different types of instruments we use, with their corresponding gage ID's: Now I would like my user to be able to click on the dropdown box under Instrument#, and that drop down box will only show the Gage ID's for the specified gage. So, if on the dimensions table, a dimension calls out for Digital Micrometers to be used, then when my user goes to select the instrument ID (which is saved in the RecordedDimensions table) it will would allow them to choose 151, 152, 153, 306, 324, 330, and 331. Please let me know if I have not provided enough information/pictures, I will gladly provide more. Thank you in advance for any assistance you can offer me!
  3. Hi...I have created a database using a "modified" anchor buoy method and so far everything works great...but...I have a portal in a "join table" that works fine...however, my button that is supposed to open the record in the portal row does not returns nothing. The records are indeed related (see RG) as the fields show up from the related table without relational conflicts, but this button that is supposed to open the "library" table with the related record pulled up simply does nothing. I have attached the DB. Also a screen shot of the table where the portal is (it is actually a join table layout, this may be the problem). I don't understand how the button (with the little arrow) does not go to the table that the actual portal record, which IS related because I see it show up on the portal row...what is not right here? Thanks! Todd **MusicalAmericaPRESUB copy.fmp12
  4. I am trying to set up a database to calculate and report exhaust emissions from diesel engines and am stumped at Step #1: The infamous Entity Relationship Diagram! I am looking at a few thousand engines and need to calculate how much of what kind of pollutants they kick out into the air. I have spreadsheets that list the Year of Manufacture, the horsepower, the serial number, how long the engine ran during a given year and how much fuel it consumed during that year. I also have some spreadsheets that list what the Emission Factors, usually expressed in pounds per hour operated or pounds per gallon of fuel consumed, for each of the two dozen chemical compounds I’m interested in tracking. Those emissions are relatively simple to calculate: Multiply the Emission Factor times the number of hours operated (or times the gallons of fuel consumed) and we’re done. Except for the fact that the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine, so I have to circle back around to the Engine table: I don’t know how to connect the emission factor for, say, Arsenic to a specific engine, say a 154 hp Chevy made in 2014, based on that engine’s year of manufacture and hp rating. So my ERD keeps looking something like this: Engines --->Emission Factors--->Engines or Engines ---< Emissions >---Emission Factors--->Engines My computer keeps puking ones and zeroes all over my keyboard when I try either setup in my Relationship Graph! The only other way I can think of accomplishing these seemingly simple calculations is to write a boatload of scripts with variables, but I’m not even sure I could come up with all of the combinations/permutations involved. And, unfortunately, I can’ even spell SQL, let alone know how to execute one of ‘em! I have attached a (simplified) copy of the spreadsheets I currently use to better explain my problem. I hope one of you will find this to be mind-numbingly simple and explain how it should be done. Thanks very much for your time and consideration. Sincerely, Guy Engine Emissions Calc Sheet MOD for FMF.xlsx
  5. Hi guys, Sorry I do not know the terminology of what I need. I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type. Eg: Supplier - Lead Type - Postcode - Count Supplier A - Back Data - AB - 20 - BB - 155 - CB - 30 Profile - AB - 55 - BB - 27 My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type. Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships. Thanks
  6. Hello! I have re configuring the Research Notes Starter Solution - it works quite well for what I need. But I want to create a couple of extra tabs with portals, but I'm having trouble understanding the how the current IDs are being created (using a calculation) for the TYPE MATCH FIELD in the Data table for Notes, Documents and Media? Could anyone help me understand this? Should I just build it from scratch? I'm using Version 14. Thanks for your time.
  7. I've driven myself more or less crazy trying to figure this out on my own. The relationships themselves are rather simple: Each project has a list of questions. Each question has a person who created the question, and someone the question is assigned to. A question also has a number of responses associated with it which each have their own "creator". There are 3 main tables to cover: Questions, People, and Responses People consists of 3 fields: The UID, the person's name, and the person's email address Questions has 2 fields which relate to People: creatorID and assigneeID Responses has 1 field which relates to People: creatorID. The way the relationship graph is set up now, I have 3 instances of the People table that relate the UID field to the appropriate ID field in Questions or Responses as needed. What I want to happen is when I set an Assignee or Creator for a question or response, I want to use the corresponding record details from the People table - if one does not exist, then create a new one. Instead what happens is a get a new record in the people table every time. Even if I set the Layout field to give me a drop down of the existing values (and I use it to select one when, say, setting the assignee on a question) it still creates a new record. So confused... any help is much appreciated.
  8. I have a solution with 4 Databases. There is the main-data-database on a Filemaker Server (only data, no relationships in the Table-Occurences (TOs)) and a GUI Database on the Desktop (only Layouts, TO's are included from the Server-Database and on this TO's the relationships are defined, so I can use them in the Layouts. Than I have a 3rd database, which is a subset of the server database and lies on an iPad. This 2 data-databases will be synchronised by MirrorSync. And the 4th Database is a special GUI Database on the iPad which uses the 3rd database for Data. The iPad GUI Database also holds TO's with relationships. Now, I need a custom privilege set to forbid changes and deletions of records when they are marked as Locked. To see all tables in the privilege dialogs, I can only do this in my data-databases, because in the GUI databases I can't see the tables in the dialogs. But in the data-databases I have no relationships defined, so I can't use related tables in the formulas. Now I have to add these relationships also to the data-databases. Is there a simple way to "copy" them from one database to an other? Thanks, Hans
  9. I have a database with each record representing one account. Within each account record, there are ten different companies the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set. Also within that single record there is an identifying ID Code, which represents the type of account it is. What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten companies, I want it to return four responses, one for each of the companies I sent it to, with the unique responses (also in a field in the main account database) with the details of the individual record. I feel like the answer is right there, but I just can't get it to work. Any suggestions would be much appreciated. Thank you.
  10. I have a table with NAME field containing Return Separated Values (RSV). I want to run a script that will add more RSV's to the same NAME field in the same table. This will be from the same NAME field on other rows. It will be based on a "match" on a PRODUCT field. I don't want to wipe out the values already in the NAME field obviously. Any ideas?
  11. This is one of the hardest thing I been trying to acomplish, I want to transform the contacts module into a Donors module for our blood bank. I have transform most of the fields already to match the donor information we will need. However I want each donor to show in a portal all the units or (products) associated to that donor. Example: 1 Donor can donate blood every 56 days 1 unit (which has a serial number associated to it) I want each donor to show all the products or units associated to him. Im sure this might be something really simple to some of you so any help will me much appreciated Thank you in advance for all the help
  12. Version 1.0.0


    Being unsatisfied with solutions I found on the internet and wanting a solution without plugin I created a set of custom functions that can read a path out of a json-string. Syntax: json_path ( json-string ; path ) examples: json_path( $user_prefs ; "Privileges/Modules" ) json_path( $json_data ; "Menus/Menu[10]/MenuItem[5]/Label" )


  13. Hi Forum, I am working on a script to be triggered when a field is modified so that it can pull data and update fields on a layout. Some data is in another file and i do not know if possible how to access this file and search for the data i need? Any thoughts on how to accomplish this? Thank you for assisting, Miss-Amen
  14. Hello, I have two tables: Companies and dupcheck_companies...Self-joined with Companies:company = dupcheck_companies::company AND Companies:pk != dupcheck_companies::pk And attached script trigger with layout (onrecordcommit) to show custom dialog if duplicate key (serial of table occurrence not empty) is found .. it works fine when duplicate company name value is found , but what if I want to check another field (phone number) for duplicates , I think it would be better if I could self-join with an OR condition instead of AND , but don't know how Please help!
  15. While creating a database that has Products, Suppliers and Lines I have come across the need to calculate a count of the suppliers from the context of a Products record. My relationship is like so: My question if I want to pull a lot of information about Supplier is should my portal in the Products record be from Suppliers or from Lines? If the best answer is Lines, could I still have a count of related Suppliers by using the primary key from that table in the portal? Is it a bad idea to use fields from a related tables portal other than its own? Thanks
  16. Hi, I'm developing for a service company that has a telesales and a field sales team. They then have a service team that visits customers and services products on site. Question: Both prospects and customers are currently in a table called 'companies' and have a simple flag (dropdown list) to indicate the difference. Challenge is that the table has grown to 124 fields with some of the fields only relevant to either Prospects or Customers. Remarkably I have never needed to create 1:1 relationships in such a scenario but I'm thinking of doing so. Would it be advisable? I'm thinking specifically it may lead to performance improvements on list layouts with some 20,000 prospect records being loaded? Also having not used this technique before is there a way of having the related 1:1 records in the separated tables auto create when I create a record in the main company table? I'm thinking this might be a really stupid question but I'm quite tired and just can't see it? I look forward to your opinions. Thank you, Simon
  17. Hi there I've been banging my head all day, several days in fact, to create a sub summary report which can reuse records, have a part appear only once and allow me to assert more behaviour over the parts themselves. To complicate the matter it's in a crosstab format as well. I've seen on other posts that what developers do occasionally when preparing reports is to create a temp table of sorts to either store just what records are needed, or to store them in the way they're needed as well. The solution I have at the moment uses lots of summing up and calculations and is intolerably slow to load locally (10 seconds at worst) although it does give me what I need. I obviously want to improve this so ultimately a second table related to the parent with just what I want, just how I want it sounds good. At this point I will add I may have this completely wrong - apologies, it's an idea I'm throwing around! The whole reason I'm looking at this is because I cannot get my sub summary report to behave the way I need it, which may be a failing on my part, but it's a pretty complex report as I've said. So, in concept, is this whack or normal?
  18. Hello, I'm having trouble with the initial set up of my relationship. For the most part it is a simple 3 table (one to many...many to one) design with a sort of "line-item" table in the middle. There are two sticking points for me:1) I would like to set up the relationships based on the "status" of the line items and 2) the portal should only show records that belong to the user logged in. I am attaching a picture to show the simple table relationship between my current tables: user, charges, patients. I have set up the privilege set so that the user can only see his/her charges when logged in, so I think I have sticking point 2 taken care of without requiring the relationship to handle it. The problem with sticking point 1 is that I want the "Patients" layout to contain 2 portals, one pulling the charges with a "current" status. I would use table occurence "Patients" for this portal. The second portal would be for showing the charges with status "archived" and would use table occurence Patients 2 (normally I would just use portal filtering to get the results I need, but I am calling the data via PHP and portal filtering doesn't seem to apply while using PHP, so I need to figure out how to do it based on the relationships). How can I incorporate the status of the Charge into the relationships between the patients and their Charges where the Patients table is related to Charges with the corresponding kf_ID_Patients and ChargeStatus = "Current" and at the same time where the Patients 2 table is related to Charges with the corresponding kf_ID_Patients and ChargeStatus = "Archived" ? Currently the patients are simply related to the charges via the Primary to Foreign key relationship, but this allows the portals to "see" all the charge records for each patient. I need them to reflect only the charges dependent on the appropriate status. Thank in advance for your help! I feel so close, yet I can't seem to visualize how to do this!
  19. Now I know you are all going to say “don’t use repeating fields to store editable data!” but I am running into all sorts of other problems with my solution using a separate table of records that I thought it may just be that repeating fields could offer a solution. Contacts table with multiple addresses. Pop List for field AddressType based on editable custom value list - Address 1 street, Address 2 Postal, Address 3 Billing, Address 4 - Shipping, Address 5 Other …Edit to generate a dynamic relationship. The current solution uses a separate Contact_Addresses table and the dynamic relationship is based on a calculation field in Contacts - AddressTypeAbs = Abs(AddressType), which returns just the numeric value from the value list. A transparent portal then displays the related address fields on the Contacts layout. This is an elegant way to save space on the Contacts’ layout as only one address is displayed at any one time but unlimited addresses are possible. My problem is however that many other tables in my solution have a relationship to Contacts, e.g. Invoices, Purchases, Quotes, Personnel and on and on and on. So in order to display address details in these other tables each has to have another relationship to Contact_Adresses. The deeper it goes the more complex it gets with relationships, workaround scripts and key fields. I thought that maybe using repeating address fields in the Contacts table itself might simplify the database by drastically reducing the need for the extra relationships, key fields etc. but I cannot seem to find a way of achieving my goal to display each repletion in a portal. What would fit my needs perfectly would be to have say 10 repetitions of each of the address fields in the Contacts table itself. The relationship key AddressTypeAbs would remain the same but relate to the address fields’ repetition number. It doesn’t seem possible to relate to a repetition number but I might just have missed something. Just thought to post this before giving up entirely. Any thoughts?
  20. Hi there, In FM13, I have a layout with about 10000 records, i would like to create another layout/duplicate and display only specific records of the 10000 records already existing on the main/old layout, i have a drop down field that defines the category of the records(e.g, Employee, Ex Employee, Accommodation, Embassy, Car Hire etc) - is this possible, by relationship definition or any other way, how?
  21. Hi Guys,  I know there are many posts about conditional value lists, but I can quite seem to find one related to how I am setting mine up - maybe its because it doesnt make sense to do it this way.  See my screenshot attached, but basically I have 3 related tables - I am using this file as a basic demonstration of what I want to do:  Class > Category > Sub Category  I want to have drop downs that filter just like a normal conditional value list. Where I would like it to change from the standard setup though, is I would like the values for Class, Category, and Sub Category to be in their own tables. The reason for this is I already had these tables and they have a fairly significant amount of data in them.  Lastly, I have my data table which is called "Line Item". Each line item I create will have a Class, Category, and Sub Category that I want to set with these drop downs.  I have been able to get my Class > Category to work correctly, but from there I cant get my sub categories to filter correctly. The Sub categories show from what seems to be, all the available options for the selected class, instead of the selected Category.  I see several of the other posts recommend putting all that data in one table. Is there any possible way to use my current setup, or do I need to just combine the 3 tables into one and re-enter the data?  Thanks!
  22. Here's a relationship / data retrieval question that I need to ask by way of example, so please bear with me. I have three tables in a system that works like an online blog. They are Posts, Tags and a join table that links the two in a many-to-many relationship - Post_Tags. Naturally a Post can have many Tags. Question: how to I get all the Tags that have NOT been applied to a particular Post? Ultimately, I'd like the data to show up in a portal, but I'm not even sure how to structure a relationship (or even a raw query) to get it. Thanks much for any suggestions.
  23. Hello everybody, I am working on a Metanalysis DB and I have currently 2 tables: STUDY (Parent) and REFERENCE (Child). The STUDY table has a primary key __pK_StudyID, while the REFERENCE Table has a primary key __pK_RefID and a foreign Key _fK_StudyID. I have set up a relationship one to many between STUDY and REFERENCE so that __pK_StudyID (STUDY) --> _fK_StudyID (REFERENCE), and I have allowed record creation through that relation. Now, as expected, when I create a Portal of REFERENCE fields in the STUDY layout table, I am able to create new records (that is new references for the same study) and fK is automatically populated. What I would like to do though is to be able to auto-populate _fK_StudyID and add new records to the REFERENCE Table using the same layout (i.e. the STUDY layout) but without a portal. If I move the REFERENCE table fields into the STUDY layout (without a portal), everytime I create a new Study, a new reference is created and _fK is auto-populated as expected. The (hopefully silly) problem come when I try to create a new record in the REFERENCE Table (that is to add a new Ref. for the same Study): I have created a simple script that: - Create a variable: $StudyID to store the specific __pK_StudyID - Go to the REFERENCES layout - Create a New Record - Set field _fK_StudyID (of the REFERENCE Table) to have the value stored in $StudyID - Go back to the original (Study) Layout. Now the script works flawlessly, and the new records (i.e. new references) for the specific study are correctly created, so that when I go to the REFERENCES layout I can see them. Unfortunately, in the Study Layout I can see only the first reference I have created, not the additional ones. Of course, when I put the same fields of REFERENCE Table in the STUDY layout inside a portal I can see all new records I have created.... Anyone can give me a reason fro that ? At this time, the only way I have to create study related references (and see them) is changing layout and that is not the most efficient way to face the problem. Thank you always in advance for your help
  24. I am having a problem in showing data from a second level relationship (not sure if this is the correct term). Here's the scenerio. Every year, I need to create new funds and expenses are created (PO, Direct Payment, Credit Card, Travel Expenses, etc) However, PO and Travel Expenses can be split funded by multiple funds. So when an expense is created it's not tied to the Fund, the Expense ID is created, and with in that the Expense Line. The Expense Line will contain a Fund field to tie it to the Fund Table. So my table relationship looks like this Fund --< Expense Line >-- Expense Now I want to show all the Expense related to the Fund table and Sum up all the Expense Line that is funded by the Fund. I have a field in the Expense Table "Total Fund" which is a ExecuteSQL: ExecuteSQL ( "SELECT SUM(Amount) FROM Expense_Line WHERE KF_ExpenseID = ? AND KF_FundID= ? " ; "" ; "" ; EXPENSE::KP_ExpenseID; FUND::KP_FundID ) However, this was not pulling up the right data. Any suggestions? Let me know if you want to see the file on what I have now. Thanks
  25. Hi, My students are developing a project in Filemaker using the separation model. The FM data file connects to a MySQL database. I noticed that when updating a foreign key value on the Presentation layer, the view of the value on the related table does not update. Like if the relationship didn't updated. Eg : I have a "buildings" table and a "building_types" table. There is a field "buildings::fk_building_type_id" which is related to "building_types::id". I am using a layout attached to BLD__BUILDINGS in the presentation layer. In the layer, there are two fields "BLD__BUILDINGS::fk_building_type_id" and "bld_BUILDINGTYPES::name". When I change the "fk" the "name" does not update directly. I must commit the record. Doing so in the data file, the update is instantaneous. What is the reason for this behaviour? Thanks for helping