Relationships

Hi, I'm importing a worked hours dataset from Excel every 30 minutes and need to find new Employee and Job records within the dataset. The Employees and Jobs also have unique ID's which I suppose should make it easier. I thought I could somehow compare the newly uploaded data to the Employee table using the unique ID and find those records in the new data that don't have a corresponding record in the Employee table? I thought this would be easy ... I tried a 'not equal' join between the ID field in the Employee table and new data Employee ID field and expected a list of data for Employees not in Employee table? Any thoughts? I'd be very grateful? Thanks…

I'm a novice with databases generally, and FM in particular. I understand relationships in FM from a basic level. However, how to leverage them within FM, and how to set them up properly, is something I'm still learning. My relationship graph in the DB I'm creating, for instance, is a bit ugly. Further, design choices I made a few months ago are not the same that I'm making today. I'd like to accelerate the learning process by perhaps picking up a book, or reviewing another resource, regarding FM relationships. Do any such resources exist?

My solution is to assist in maintaining records in my computer repair business. It has customers, who have devices, which have service records. I sometimes buy devices from customers, or the device may be reallocated to, for example 'recycle' or 'parts'. I would like to be able to reassign the owner (via a dropdown) of a device, maintaining its service records but also keeping a log of previous owners and the dates of reassigning. For example Customer 1 owns Device 1 Customer 1 decides to sell Device 1 to my Company on 1/12/14 Company refurbishes Device 1 and sells it to Customer 2 on 14/12/14 How might I go about implementing this please? PS: Very …

Looking for best solution for a medical record application. In an allergy office, patients have skin testing with a panel of allergens (bermuda grass, chocolate, etc). The reaction on the skin to each allergen is measured and recorded as two variables, wheal size and erythema (redness). Each patient may have a full panel (up to 66 tests) or just a few, and each patient may be retested at a later date. I have a table with the allergen list (about 100 records), a table of patients, and a results table with four measurement fields and test date, linked to the patient table. Results are first recorded on a paper chart and then entered into the DB. I h…

I'm putting together material (tutorials, etc.) in order to establish a relational database for classical CDs. It should necessarily be a many-to-many, when a CD having one or more composers and one or more artists, and these may occur on one or more CDs. All this is trivial, but how do I handle pseudonyms and name variations? Example: The composer Dmitri Shostakovich's name is / can be written in many ways on the many CD releases. When I am in my CD database and draws information from the composer database, the data here of course only should be registered in one place and here I choose the English / international spelling by default. I suspect that the default name a…

Hello I have a series of audio files named as follows: 20141205220512 (the creation date timestamp of the file). I need to change these file names trying to link them to the corresponding video files filmed at the same time. Both files where created within one or more minutes or few seconds time frames. In any case there are no overlapping timrs between creation timestamps while the supposed range might vary even a lot, Is there a way to create a relationship between timestamps within a range? I created two FMP databases with the file names and timestamps with name, creationdate timestamp, but obviously I am unable to relate them as the timestamp are never the same. I f…

Hi I make a database for the stones of Nidaros Cathedral in Trondheim/Norway. Would like to make a container field that shows a image based on values in another field. Detailed description: I have 200000 stones with unik numbers. There is a field that shows a number for which part of the walls the stone is located. I want a container field that shows a map/photo, based on the numeric value. Anyone with a easy detailed explanation?

My database concerns patent applications. Some applications can be related to other applications in a way that is quite similar to a family tree. In particular, any two different applications can be directly related to one another in a hierarchical relationship, where one is higher ("primary") and the other is lower ("secondary"). The type of relationship can also different, there are at least three or four different kinds. I currently have a table of patent applications. To add in this type of relationship, I was thinking of constructing another table, call it relationships. Each relationship record would specify a primary patent application, a secondary patent …

My filemaker database is about patent applications. So I have a table of patent applications, one record per application. Now, two or more applications can be in the same family. A given application can only be in one family. So, say we have patent applications Alice, Bob, and Carol. If Alice and Bob are defined in be in a family, and Bob and Carol are defined to be in a family, then there is just one family, including Alice, Bob, and Carol. I have absolutely no idea how to set up such a "family" in Filemaker, nor an easy way to specify a layout object so that a user can, when in Alice, for instance, specify that Bob and Carol are in the same family, such that…

Hi folks, I have the following tables: Pupils - details of the kids Classes - details of classes etc they're members of Assessments - general details of assessments; what academic year, what title, maximum mark etc. PupilClassAssessJoin - the specific record of each assessment for each kid. At the moment I have an Average% field in Pupils that gives the average of all their PupilClassAssess records. Â However, it would be useful to know what their average is for the current academic year (set by gAcademicYear in the Classes table). Â I've set up what I thought would filter on assessments from this academic year to give me the results in a field in Pupils calcula…

Hi, am trying to move from Access to FM.(And I'm a painful beginner) I have 2 tables: 1 with PatientDetails (mother table) and the other with VisitDetails (child table). While the AutoSerial no with a +1 increment works for all new Patients, I'd like to know to lookup existing patients and add a unique serial no for each visit, while maintaining the PatientID (which defines the relationship between the 2 tables). Is there any way of automating this.... With Access this was a no-brainer.... Thanks

Hello File Maker Community: I am working in a data base with different schools with several layouts. Each layout have different information like Google maps, Websites and data. All different sources of data have a common key (school number) which has been used to create relationships. The solution works well all the information shows as I wanted. However, I want to create a search menu to select one school then all layouts will show for that school. So far I am able to create a search but only goes to one layout, the other layouts show different schools. I hope I can get support to find the solution to have one search for multiple layouts. Best, JEst…

By this I mean If I had a sentence. " I love the color .........." The relationship word is color but the possible answers might be red, blue, green , yellow orange white , maroon. Can this be done somehow? The lookup may have a random answer within a list... Not sure what call this.

Hi all -- I just switched over from Access to Filemaker, so I'm a total novice -- bear with me! I have two related tables, Staff and Vaccinations. I have about 90 staff, but I only have vaccination information for about 40 of those staff so far. The problem is that I would like perform searches for staff that are missing vaccinations. If they have no vaccinations on file, they don't have a record in the "Vaccinations" table -- unless I manually enter their ID number and leave the vaccination stuff empty. So right now, about 50 staff are being excluded from the vaccination search because they don't have any information in there. Is there a way to automatically popu…

Hi Everyone, I am adish, new to this forum.please guide me if i do have many warehouses around 100 at different locations and doing stock transfer among them.then how we can calculate products on hand balance rapidly for all hub or individual hub.Now i am adding all quantity in (from po,return,open grn and other) and all out(Stock transfer,sale,demo etc) then calculating which is taking a long time and also all inwards and outwards transactions happening barcode wise only so i always have to tally product quantity vs barcode quantity please suggest Adish

I am working on a system that will take orders and find the warehouse that has the stock to fulfill the order. We have 19 warehouses that hold various different items, Based on inventory some items are at all warehouse some are only at one, some are at none. I have created a portal that allows me to search for a SKU and see all warehouses that have the item in stock. I would like to be able to import the orders as csv - create a record for each order - then show the warehouse that has all items available. Current structure: INVENTORY: Table with SKU, Warehouse Name, Warehouse Number, Quantity On Hand WAREHOUSE: Table with Warehouse Name, Warehouse Number…

I have a Customer who has many devices. Each device has many Services. I want to be able to display Services that are related to the owner of a device in a portal on my Customer layout so thay I do not have to go to my Device layout first. I am having a complete mind-block over this, I know it can be done, I am pretty sure that I covered it while learning to use FM but for the life of me I cannot implement it. Any help please?

Hello I'm pretty new to Filemaker.  I'm using FM 13 Advanced.  I've taken 2 courses on filemaker on Lynda.com.  I feel that I've got a good grasp on the application, but I'm certainly still new, and still learning  I am designing a database for my group insurance business.  I'm working on the Relationship Diagram, and I think I have most of it sorted out, but I've run into one problem.  I'll start by describing how the DB will work.  Each one of our Clients will have a minimum of one Policy.  Each Policy will be through an Insurance Carrier.  Each Policy will consist of the lines of benefits that the Client has chosen (ex. Life Insurance, Health, Den…

I have a table that has a "name_field". This table will have records where the "name_field" will contain the same value (name) as other records in the table. I would like to use a portal to display all records where the "name_field" contains the same value (name). I thought a self-relationship joined at the "name_field" would do the trick, but, it only shows a same value (name) when you are on a particular record. I understand this. What I don't understand is how to get the portal to show ALL records of the same name, regardless of which current record I am on. Thanks in advance.

I am new to filemaker pro and have a specific question here. I have four different tables, each with an individual layout, that are gathering information on a specific transect line (we are collecting botanical data on many transects along rivers). I made a "general Information" table with a layout that has specific information I need to gather on each individual transect. The other tables are there to enter different data on individual transect lines. Here is my question. I created an ID field on my general information page to identify each transect. Whenever someone enters info into a record on other tables for that transect, they start by entering the first field on th…

I am taking my first look into containers. I would like to add the ability to tie multiple photos (taken on iPads) to our inspection records. The most logical approach to me seems to be to create a child record with a single container each time I wish to add a photo. I've looked for discussions along these lines, but not found anything that directly relates. Am I on the right track here? ANY suggestions would be welcomed!

Hi everyone, I have a database with the following tables: Table 1: Projects (ProjectID, EstimateID, Description, ProjResp) Table 2: Estimates (Project ID, EstimateID, ActivityID, CreationDate, LastModified, TotalCost) Table 3: Activities (EstimateID, ActivityID, ActDescription, ActResponsible, ActCost, Spent, Balance) The way I have my layouts designed, I have a layout #1 (main menu) where you input/view the info for every each project (from table 1 - Projects), but it also has a portal that shows the estimates (from table 2 - Estimates) that have been done for that very same project. Then I have another layout (from table 3 - Activities) with the activities…

Hi I have two files: FileA with near 4 millions of records and FileB have 140000 records. FileA have fieldA_ID of 11 digit number and fieldB_ID on FileB for matching purpose. The idea is to match FileA, fieldA_ID to FileB, fieldB_ID for getting the values (name, in this case) from FileA. Example: If fieldA_ID of FileA MATCH fieldB_ID of FileB then get the value of the field "name" of FileA. For this I create a relationship to match both field_IDs of both files but don't works. Could you please upload an example for this procedure? Thank you Tom

I have a table called Projects which has a dollar figure attached to it. I need to be able to debit an amount from one project and apply it as a credit to another project. I've created a relationship like so… Projects --< CreditsDebits > -- Projects where the CreditsDebits table has foreign id fields for "id_ProjectsCredit" and "id_ProjectsDebit" (both are the id field from Projects), as well as an "amount "field. I'd like to show credits and debits in a single portal on a Project layout. Is this possible? I know I can have two portals (one for credits and one for debits), but I'd really like to combine these if at all possible. Thanks! Michael

I am creating an in-house solution where we keep track of (among other things) contacts and am at the moment working on adding functionality for storing address history for our contacts. I will take care of all editing with scripts - my question is about best practice regarding storing and displaying the data. My idea is to: use a related table and timestamp both creation of a new address and when an address is no longer the "current" address. This would allow me not only to see previous addresses but to identify which was the current address at a given time. have a plain field in the layout for displaying the current address. The question then is: Can I be…

Hi There, Hoping someone can explain what is happening. On my home layout, I have a table linked which has a calculated field called "Current_Logged_User". It basically populates who is logged in using Get ( AccountName ). There's another calculated field called "CurrentWeek" that calculates the current week number using WeekOfYear ( Get ( CurrentDate ) ). Both fields are unstored. I use those fields to create a relationship to another table for KPI purposes. I further have a popover on the home layout which when opened runs a script that checks if there's a record in the table for that account with a matching weeknumber. If not, it creates the record. The idea is that…

Hello, I am making a database for Bone Marrow Transplants and I am having trouble with some relationships. Currently I have tables: Patients, BMT, Collections, Regimens I have related Patients to BMT in a 1:many relationship which is fine and each BMT can have multiple collections (works too). What is tricky though is that each BMT can have multiple collections spread over a couple of days. That means each record in the collections table will be for a certain collection day regarding that BMT. That would make a 1: many relationship. Now each collection will have multiple regimens... but the catch is the collections related to the specific BMT will have the same mu…

I am trying to create a data base for fantasy football. Â I am sure I am not the first nor am I the last. Â Â I am attempting to take all available players at a given site, and calculate value for each player. Â I am then going to sort by position to help the selection process each player. Â I have my tables getting data from a specific excel spreadsheet and importing it into a FM table. Â I am then performing calculations to come up with predicted performance. Â I next want to take that predicted performance and calculate to "fantasy points per dollar" thus finding the most value. Â Â I have two tables set up as seen in the .jpg. Â I can not relate the two ta…

Hey guys I am having a very wierd problem. I am on FM Server 12 with FM Pro Adv client. I build a record in "Company" and give Company a unique key, say Company.Key I then build a record in "Phone" and give Phone the Company.Key So. Company::Company.Key = Phone::Company.Key The tables WILL NOT relate until after I go in and change something on the database. So, say I am on the company form with a portal to phone. After I build the records, the portal is blank. I go in to the database, change something, save, come back to the company form, and voila, the portal is now populated. No amount of clicking around, committing, or anything else, …

Hi, I believe I'm having a problem with an unidentified key concept. I hope to get pointed in the right direction. I am attempting to build an English horse racing form here in Tokyo. I import all the data from Excel and am working from a single table (I believe this might be the root of my problem). I did a few tutorials and I felt pretty comfortable with the basics. At my first attempt (and I thought it was strange at the beginning), I gave every piece of info that would appear on the form its own field. With a possible 18 horses running in a race, and 5 lines of past performances per horse, I ended up with almost 2,000 fields. I filled in each past pe…

Hello, I am having trouble wrapping my head around this concept: I am building a Bone Marrow Transplant (BMT) database. In it I have 3 tables that are important for the purpose of this question 1. Table 1 Demographics Fields: pk_demo, Last Name, First Name, Chart# 2. Table 2 Diagnosis fields: pk_Dx, fk_demo, disease, other fields that are not important 3. Table 3: BMT Fields: pk_BMT, fk_Dx, IUBMID, Date etc Relationships (so far): Demographics:pk_Demo--->Diagnosis:fk_demo Diagnosis: pk _Dx---->BMT: fk_Dx What we have in this database is a bunch of patients (both donors and recipients) for Bone Marrow Transplants. Each patient …

Hi folks, I've got two separate databases. has tables of pupils, classes, assessments, teachers & instruments etc and is used for day to day registering and grading kids in class and in ensembles. The pupil table has all the pupils in the school and the ID is their school ID which I have been assured is always unique. Is flat. It's used to hold the results when I audition potential music scholars to give me a rough ranking order. Each record is one audition with its own UID. If the same kid auditions twice, that's two records. Not all the kids that audition come to the school. Ideally I'd like to combine these two; so that I can look back at current kids …

I am building an interactive quoting application for my client - they are in the business of roofing. There are many intracacies to how their inventory items relate to eachother. For example, 1 type of guttering requires a bracket every 700mm. Not all brackets are suitable for all types of gutters. So in the inventory table, I have created a portal titled "Suitable Brackets" but this only displays if the item type = Gutter. This seems fine for this level of complexity.. When we look at a more complex situation - if the bracket type is not "External Bracket" then a clip is also required with the bracket. What type of clip? Well that depends on the type of F…

Hi All, Â I need some help and have attached a whiteboard diagram that hopefully shows better than what I can explain with words as to: Â 1) What I currently have set up in FileMaker (2 tables: Domain Types and Domain Members) 2) What I need to be able to do (create compositions - I think) Â This database has been functional and very important to be able to build and see what "things" are grouped together by "type". This is a little loose in the definition because of the broad type of information in which I am storing. What it basically does currently is allow me to keep a simple 1-level classification of things, for example: Â Domain Type is the name of the …

Are there any drawbacks to creating a second occurrence of a table? I have hunted around through documentation and help and I can't find a concise discussion of what the negative impacts of it might be.

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 an…

Good evening, I was wondering if I could call upon the help of those of you whom have helped me numerous times before, with what should be a simple problem. Attached is a solution that I am putting together. In essence, its a simple thing, but I have mucked up the relationships somehow so it doesn’t work correctly and I need help. Iwould have thought it be a simple thing with a Count and Sum, but not so… In short, its a roster for engineers. At the top of the page is a list of fictitious clients, below are a list of engineers that support them. In the outlined boxes in the client part of the screen, a user can drop down the list of engineers assigned to tha…

Thanks for your interest! Trying to figure out a clean and solid way to make a value list of only invoice with an "unpaid" status. In invoice table I have a calculation field for status. Status field references payment table, if amount payments = amount due; "Paid"; "Due". So, since the status field references a related table, it appears it can not be indexed which breaks the value list. I could always base the invoice status calculation off say, a date paid field. But if i do that, then now I have to enter a payment record, payment record date, and also enter a payment date in invoice record. Just trying to keep extra steps/double entry down of course. A…

I have a little database that the sales people here can access via IWP (it's a FM 12 Advanced database). I have provided a function where they can look at a list of new companies, and assign themselves to those companies. This has the effect of showing that company on their Dashboard. I have also provided the rather useless function of hiding the portal row when they're looking at the list of companies. Useless, because as soon as they revisit the list, anything they've hidden comes back again. I would like to provide a feature where they can click the same 'hide' button, and that company record is permanently hidden from view per user. So Adam hides it, but i…

Hello, I am very much a newbie to FileMaker and I appreciate the help in advance and i'm sorry if this is a very confusing question (or if I am so new I don't know how easy it is). I have one database "StudentData" that holds, essentially, contact information for our students (biographical information, whether they are active or alumni in the program, etc.). I need to create a new database/table "AnnualReport" that takes biographical information from "StudentData" IF they are labeled as active in a specific field in "StudentData" and also has space for new values to be entered. I'd also like information the record in the "Annual Report" to be deleted if the student is…

Hi all, Need some help. I have table called PARTY RELATIONSHIP that records any of the three scenarios: 1) a relationship that a person has with another person (JOHN to JANE) 2) a relationship that a person has with an organization (JOHN to ACME INC) 3) a relationship an organization has with another organization (ACME INC to XYZ CORP) In addition, the PARTY RELATIONSHIP table records: 4) the ROLE TYPE each person or organization plays in the relationship (HUSBAND and WIFE); AND 5) the PARTY RELATIONSHIP TYPE the ROLE TYPE combinations selected above create (MARRIAGE) EXAMPLE RECORDS: A: JOHN plays role of HUSBAND in MARRIAGE to JANE who plays role of…

Hey Group. I have a DB that has a global settings table. In the table I have settings for the app. Company name,address and such. Also a logo. I use these in the reporting so I can customize my invoices and purchase orders. Recently the main record of the global settings was deleted. I brought back the record but now none of my reports will work with the global settings table. I have the exact records back now but something is off. Any ideas? Thanks Jamie

Hi there, I have a tendency to create too many relationships in Filemaker. Currently I use globals to set up portals that need to see all related records with a particular status. I know I can use portal filters but if the related table has thousands of records and I use a filter it takes ages to index and display. I also like having portals that change what they display based on a Finder field that forms part of the relationship. For instance "courseNameFinder" where you enter the first 4 words of a course name is related to a field in the related table called "courseName1st4Words" which is a calculation e.g. LeftWords(courseName, 4). So I need a separate rela…

I hope this makes sense... I am trying to import data from a previous database solution with a slightly different approach to our data. The data is stored i just two tables. One table contains all the people in the database, the other table contains more or less all the actual data about the persons in the first table in value pairs. So for instance the phone number for a person is not in the 'people' table but in the 'data' table. My problem is that this makes it difficult for me to import the data into my new solution where I would have the phone number (and all the other data) in the "people" table (as I have no need to store more than one phone number etc.…

I have a Bill of material (BOM) structure with 2 files: File A which contains all items (Finished products, Semi finished products and purchased items), and the fields are item-number, item-name and various descriptions File B is related to file A by Father number = Item-number and contains item-number, item-name as the A file. File B i populated via a portal where the user selects from File A items and specify quantities. A Finished or semi-finished production can therefore be comprised of a tree structure of items: O ------ S --------- S -------- i l l l l -------- S l …

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…

I am stumped with this problem. I am sure that it is just me being dumb but I could do with a hand. I am trying to set up a file which displays : • the scenes in a play, with the name of each actor and each role in each scene. • the names of the actors with the scenes they are in and the role(s) they are playing in each scene • the names of the roles, with the names of the actors playing the those roles and in which scenes. In the attached file, steps one and two work fine, but I can't figure out how to accomplish step three. Would someone be kind enough to put me on the right track? Actors in Macbeth.fmp12.zip

Hello, I am in the planning stages of building a Contact / Marketing Outreach solution, and it has become clear that all my tables need to interrelate in a many-to-many way. It will have: Individuals Entities (i.e. companies, organizations) Lists Notes All these things interrelate in a many-to-many way, so they require a join table (though Notes might be an exception, as any given note would only relate to a single Individual, Entity or List). What I'm wondering is if it makes sense to use only one join table between all 4 of these things and use complex relationship criteria, scripts, calculations, and filters to build out all the requisite layou…

I have a table called services. I want to be able to create a portal inside that services layout that allows me to relate one service to another. I want the service that i related to also show that relationship in the portal on its record. So in other words lets say i have three services. A B C If i relate Service B to Service A in the portal I want to be able to click on Service B on the services layout and see that it is related to service A. Currently if I relate B to A on the A record, when i go to record B it does not show that it is related to A. Thanks for the help guys! -Erik

Hi Board! I've read a couple of threads on this, but i'm not getting it.... I have a Months table, that calculates the months (36 records for historic charting). I have a joined table occurance, 'Products_Checked'. This lists all the products that have been checked, other details, and their date/timestamps. I want to chart a Count Per Month of Products Checked. But i want to only count certain products - namely with a stock code that starts with ' 50 '. I have a Global field in the Parent Months table that is my 'Search String' but if I put 50 into it, the graph doesn't work. If i remove the relationship for this search, then the Count works for a…

I'm working on a invoice payment function. There are invoices that have been issued, and when a payment is received at head office it can be for the full amount for a single invoice, a partial amount on a single invoice, the full amounts for multiple invoices, partial amounts for multiple invoices. I've got a Payment Header record that has a field for the amount of the payment. The user records it as one of the first steps. I've got 1 to many Payment detail records that have fields for the related invoice that they payment is for, and the amount that is being applied against that invoice. I want to verify that the total that has been applied to each invoice …

Hi all, NightWing Enterprises has an interesting demo called 'Instant Search'. It lets you type in a a list view of records into a global field and matching records are shown as you type, similary to a web search engine. I wonder if it's possible to do something like that in a portal, as I'm starting to move away from the list views. I prefer a record view, but with an all-match relationship portal off to the side to quickly switch between records without switching layouts. It would be great to have, say a search box in which you can type 'a' and all records that start with 'a' on a particular field are shown. Continue to type 'af' and only records starting wi…

I've got a database of items (records) with various components. Many of these items share components. I'm trying to create a related table that will show the count of the items that share 3 of the same components. I can manually do this with a script, but what it has to do is loop through the various combinations and count the results. The script takes takes time and is annoying and of course does not update as additional records are added. Example Data Table: Part# | Component 1 | Component 2 | Component 3 | Component 4 | Component 5 PartNo1 | A | B | C | D | E PartNo2 | B | C | D | E | F PartN…

Hi, first time to the forum. I've been learning FM on and off for about a month, now I'm stuck. I have 3 tables (Student, StudentClass_Join, Class) for a "many-to-many" relationship. I made a layout based on Student, and on the left-hand side I insert a portal controlled by a drop-down list to list all the students registering a certain class. When clicked, an individual name on the list will show the detail information of that individual on the right-hand side. The Problem: Only part of the candidates registering that class will show up on the portal list. The portal filtering design is based on the post "http://filemakerinspirations.com/2010/10/dynamic-p…

My goal is to have a notice system where each staff member reads the message and clicks a button saying they read it. A report is generated where I can see which staff members have read the notice and who is slacking off. I have so far 3 tables. Staff, Notices and Staff_Notices Staff: ID, Name etc Notices: ID, Date, Content etc Staff_Notices: Name, Notice_ID, Status I am very unsure where to go from here. The Staff_Notices table seems to be pulling information from two tables which I haven't done before. Maybe I am on the wrong track. Maybe there is a template that could help me. Thanks anyone for reading my SOS

Hi, Â I am designing a FM12 database to allow people report their progress of work. I've seen a database design that allows customizsation by the users (not just admins) to create their own question templates, which will then appear in a separate reporting page for users to fill in. Building on this design, I wonder if it is possible to give users the ability to choose which questions they would like to answer. I am however at a loss as to how to create the relationships and designs to achieve such functionality, and would really appreciate your expertise in the matter. Â From the database design I've seen, the users can essentially access a layout to create a templ…

Hi  I'm not sure exactly where this question should be asked on the forum as it's more of a "Can it be done?" sort of query and if so, how, so I apologise in advance if its in the wrong place.  Let me explain. I have created a social car database for a charity that matches elderly and infirm people that need to go to various appointments such as doctors, dentists, hospitals etc. with volunteer drivers. The drivers themselves use their own cars and give up their time freely to the help the charity on various days and times each week. This is all co-ordinated using an FM Pro 12 DB where clients are held in one table, destinations in another, drivers in another and…

FMpro Advanced ver 12.0v4,   I created a new database "Calibration Assets (CA)", which I imported some data into from an Excel spread sheet( filemaker assets list 7_8_14.xls).  I created another database " Calibration ( C )" and would like to pull in some data from CA.  When I try to create the relationship in C, I pick the CA db in the specify table window and when the file opens it's blank?  Where are the fields?  I have attached a jpg so you can see what I am looking at.  FYI, I was able to create a relationship to the Excel spreadsheet, but, not the CA db.     I really appreciate any help you can give me.  Thx Lupe

Hi, I hope someone can tell me a simpler solution to the following problem. I have a table that keeps track of people, each one has a unique ID number. I have a separate table that keeps track of dates of meetings. Those two tables already exist and contain data. I also have table that tracks each person's attendance at meetings and other notes that can be made. This has zero records in it so far, but I need a script that will create a record for each person at each meeting. It will have a field that links the record to a person (via ID number) and a date. I would like a script that efficiently and effectively creates all the records in this third table …

Dear FM Developers, I do have a problem to build a report to show the ingredients which are necessary for order. The structure of the relationship is as follow [order] customerID orderID [orderLines] orderLineID orderID (relationship to order) productID quantity [products] productID (relationship to orderLine) recipeID [recipes] recipeID (relationship to product) [recipeLines] recipeLinesID recipeID (relationship to recipe) quantity materialID (points to productID in TO of products named materials ) ok real example would be the customer places an order for #order 1 -- Green Drink - 2 bottle -- Red Drink - 1 bottle …

hi everyone. i'm new at this forum and i'm beginner user of fm. i'm not sure about this is the right topic for that , if it's not sorry about that.  as you see i have file including 1656 record. i have to split that records as  0 to 1243 and  1243 to 1656. i can't find way to this. could you help please?  thank you already.

I am creating an invoice database. It is a standard invoice solution with one minor glitch. Each customer CAN have a different price with each product. To solve this i created a join table between products and customers and put a portal on the customer page where the user can from a drop down change any price manually. That works fine. But now when I try to create a new invoice the invoice table doesn't insert the customer name-address etc. Here is what I have. I cant see the forest through the trees on this one... can anyone give a hint. thanks

I'm not sure if I am missing something obvious or if I'm trying to do something that can't be done. I have two tables: Contacts and Estimates In the Contacts table, there can be multiple contacts with the same company name. The Contacts table use different records for different people to accommodate possible multiple detail differences (email for sure, but also possibly addresses and other details). I have set up the Estimates table so that using a Conditional Value List, the selection of NAMES shows correctly whenever a company is shown, but I also want other fields to populate based on the name selected. For example, when I select Acme Test Company and tab t…

Howdy, howdy... I have a script that changes the color of all the fields' text in a record whenever that record is selected (while in List view); it's set up using the script trigger, OnRecordLoad, in Layout Setup. Works great. However... I want to use it the same script for a child table from a parent on a different layout (and of course, table) with a portal connecting the two. OnRecordLoad won't work in the parent table since it has its own layout and OnRecordLoad only works on that table's/layout's records. So, how do I fire the script so when I select a record in the parent's portal it'll fire the script to highlight a related record's text? TIA for y…

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 …

dear All, I want to ask some question. How do the same name and with difference eyes show in the portal. Untitled.fmp12.zip best regards Dedy

Hi, I've been lurking around the forums for a while now looking at various topics regarding database schema for Survey applications. However, despite the insight and templates posted on this forum I still cannot get my head round how to create a relational database for my application. Here's the scenario: I need to create a health and safety inspection application which will enable the user to carry out a survey of how well a specific location fares against a bunch of pre-set questions/criteria. So pretty much a checklist where each question is either a pass or fail response. Currently the survey is paper based (I have attached an example to this post) As…

Hi. I'm assuming this is the right area to post this particular message. I have an issue where I am unable to delete records, and am presented with the message: "This operation cannot be performed because one or more required related tables are missing". I know the message is fairly self-explanatory in it's language, but how do I find the offending items/connections to resolve it? Greg

Is there a way to delete records from a related table using a button. a script to find a curtain number of records in a related table and delete them I started with setting a variable from the current record to find the ones in the related table find records[] but i cant get the search working and is there a control to delete found records Thanks Really appreciate it. Mg

Hello - First thanks for taking the time to offer your input. I appreciate it. I am a novice to filmmaker but have spent a great deal of time researching it and studying it. I have built a couple "trial" databases. I am now working on a database to hold all of my real estate business information. For the problem situation at hand I have three tables I am looking at: Contacts, property Address, and a jointable for them. I would like the home address or mailing address to be supplied on my contact cards. But many contacts will be linked to many different properties. So I am assuming I will want a field dictating which property is a "home address" in the prope…

Is there a way to create a relationship between tables on the fly in a script so that you don't need to create a table occurrence. Any plugins? Our relationship graph has 300+ table occurrences (anchor buoy), and we'd prefer not having to create table occurrences unless they are going to get a lot of usage, particularly on maintenance scripts that will be only used once. I know we can create a table occurrence and then delete it, but there's a tendency to forget to delete it...

I'm trying to fix a problem that has plagued me for a couple of years. Â I've work around it in the past but now it is really in the way. Â I made a backup and deleted all instances to start fresh. Â I have a customer table and a dealer table. Â There are two different products that are serialized, each having their own table. Â When we sell to a dealer or a customer their record is linked to the serial number. Â When a customer sends a warranty card to register, that record is add to the serial number. Â So many times the serial number is linked to both a dealer (purchased through us) and a customer (purchased through dealer then sent in warranty card). Â The issue…

I just loaded the latest version of Filemaker 13 Pro Advanced and I lost the scroll bars in my relationship graph. Anyone else experience this and is there a fix?

Hi When using GTRR to navigate to a record in the same list view layout, the destination record is displayed in unpredictable vertical locations on the screen. Sometimes it will be displayed at/near the top of the list or in the middle or right at the bottom of the screen. Thus the user doesn't know where to look first to locate the record on the screen - it's annoying to have to visually scan the screen. Is there a way to force the target record to always display at/near the top of the screen, irrespective of whether it is down the list or up the list? I know there is the Scroll Window script command, but I can't think how it can be used when you can't predict w…

Hello, I hope I will be able to explain myself well enough to get your help. I have a simple table [Log Entries] with student names, their unique ID numbers, discipline incident date, discipline type, consequence, date reported, and reported by (teacher's name). I want to match the ID numbers with Grade Levels I have from a different source. I want to either import table from file and match or I can relate them somehow. Here's the problem. Each record could have duplicate IDs but are unique from each other. Therefore, when I try to match for Grade Level, I get no matches. I think it's because the ID is used multiple times, but I don't know how to correct the problem. …

Hello all, I am working on putting together a system to track event sales. So here is what I have so far and what I am wondering is the best thing to do. I have made three tables. Venues, show dates, and daily sales. Venue is a list of Venues with details Show dates if the list of events daily sales is where the daily sales for each event will be updated and stored. What I am trying to figure out is the best way to updated the sales numbers. What I want to do is have a page with all the upcoming events listed and be able to enter the new sales record for each show. Can this be done in a portal? any and all help is appreciate…

Hi,  First Post here, look forward to meeting you all.  I'm doing an electronic Medical Record for my own use. I'm a bit stuck on my relationship graph. Patient_UUID is my foreign key in tables used to create x-ray forms, prescriptions etc. I wan't to expand to make my database multi doctor and have preferences for each Doctor. e.g. address, templates, macros, letterhead etc  Pref_Doctors is my doctors table with a Primary Key of Doctor_ID. I obviously need to link this to Prescriptions to allow the doctors address etc to load on the script as a merge field.  My question is do I need to do a new TO of Pref_Doctors for each item I wish to link? e.g. opera…

Three tables in question are [CUSTOMERS], [uPDATE], [PRODUCTS] the relationship is many customers to many products with [uPDATE] being the join table. The only field in update is the primary key, the two foreign keys, and a date timestamp. In the [PRODUCTS] table i have a (GROUP) field. In the [CUSTOMERS] table let’s assume there are no fields that will matter in this except the customer ID When i look at the Customer Detail Layout, Which is related to the [CUSTOMERS] table i have Products related in the many to many direction as mentioned above in a portal. In this Customer Detail Layout, I want to show a graph chart that counts how many products the …

This is not such a rare situation, but I'm puzzled, about how to list the count of related records in a summary part. The setup is like this: I have a table of "Boxes" that is related to a child table of "Items" via the key field pk_box. However, boxes are also grouped by box color (say red, green, blue, etc.). On a layout of item records, I have placed sub-summary parts that appears when records are sorted by color and pk_box. On this summary part, a summary or calculated field can display the number of items in the found set for each box color. What puzzles me, is how to calculate the number of boxes of a particular color and list this in the summary p…

I'm trying to create a report that shows mee all Tutors that are not tutoring a course. The tables involved are: Tutor --> TutorCourse --> Course Where TutorCourse is a join table. It's easy to see which tutors have courses as that is prcisely what is stored in the join table. The approach seems to be to interate through the Tutor IDs in the Tutor table and see which one's do not have a relation to TutorCourse... but I don't know how to do this. Is this even a good approach?? Jason

Hello all. Have been working with FM for a bit, but still have very basic problems with relationships— help appreciated. Background Work in a laboratory and am needing to create a db w/ labs, tests, and report content Functionality: The user example: tech from LabID= 2 (Molecular lab) ) should be able to select TESTS from the lab in question example: JAK2 test then select the result ( positive / negative etc) and that selection should populate results (Test…

Hi, My first post on this forum so hi. Im a bit stuck as I have been playing around with filemaker pro 12 and I have come up against an issue. Can you advise me please? I currently have two tables, People and Criteria. The people table consists of something like this; Person ID (unique serial key) Name Age address Criteria 1 Criteria 2 Criteria 3 The Criteria table is a bit like this; Criteria Number (unique serial key). Criteria detail. Criteria Year Basically I want to be able to enter the criteria number in comment1/2/3 and then create a report on this showing the Criteria detail. The issue is as I am sure you can see I have …

Hello! Need some basic advice if you don't mind. In my database (FMPro13) there are 2 linked tables, [Hits] and [Runs]. The [Hits] table includes: <idHits> index field, autonumber <Title> text field, title of the song <id2> extra field, a number Table [Runs] also contains a number of fields including: <idRuns> index field, autonumber <idHits> number field --> this one creates a relationship between both tables, created a link <Place> position in the charts, number field <Date> date of the weekly chart compiled <id2> extra field, a number I'd like to move c…

Hi, in my order layout (see below) I want to show the amounts of the invoices, but they are reluctant to show up. Only when I switch to the single order layout and have the amount display there, then switching back, they show up in this list layout. Any idea why is this and how I can have these values to be displayed always? The value/field in question is a calculation field that looks like this: RG_OffenerBetrag_oder_Gesamtsumme: Case ( Listenindikator = "R" or Listenindikator = "O" ; RG_OffenerBetrag ; AuftragZeile::Gesamtsumme_brutto ) RG_Auftragsumme and are RG_Anzahlung are both number fields AuftragZeile::Gesamtsumme_brutto is a calculation in the …

Not sure if that was a very clear title, but I wanted to be adequately descriptive. I have a db with several tables, most of which are more reference tables to make data entry easier. I have a table called shows, with each show a primary key. I have a table called classes, each class with a primary key. I have a Show|Class Join table where user chooses from drop down lists from each of the above fields as foreign keys, and also shows the "name" field vs. the number ID field. The Show|Class table designates each show and class combination as it's own entry. I am trying to create a new field in this table which pulls the NAME data from each of my fore…

Hi I have 1 layout (customers) with customer details in it, but i want to place a button on (customers) layout that would take me to layout 2 (invoices) and insert the same customer id as well as create a new invoice id record i already have Customers table field name __pkCutomersID Invoices table field name __pkInvoiceID & _fkCutomersID related to __pkCutomersID i have tried many ways but with no luck Thank you all in advance

I have a customer that I am creating new DB for, but until then, they are using their old one. They have this problem with their old one that when they do a Replace Field Contents it gives the error: "Communication with the host was interrupted and could not be re-established. All affected windows will be closed." It only crashes when their one field is categorized by certain data. If that field has any other data beside that one criteria (and they do a find to get that into a found set), then when they do a Replace, it does not crash. The replace is not being done on the one field, it is being done on separate field(s), which are repeating field(s). It do…

Greetings All FileMaker Friends; I would appreciate help for the following: How do I combine datas from 2 or more Tables, in different files into a my current Table, without using "Import" option. Thanks in advance. Lawrencex

Hello Guys, i’m a novice in the filemaker world and as every new challenge, i don’t miss to get stuck in some problem, called “script”. Let me explain: I have got 5 tables called : 1. Table “Company” 2. Table “Customers” 3. Table “Terapy” 4. Table “Trap” 5. Table “Report” They are related as follow: 1. Table “Company”:: Id company…… X ….. “Table “Customers”:: id customer 2. Table “Customers”:: Id customers…… = ….. “Table “Terapy”:: id customer Table “Terapy”:: Id terapy………….… =. ….. “Table “Terapy”:: id terapy 3. Table “Terapy”:: Id trap…… = ….. “Table “trap”:: id trap 4. Table “Terapy”:: Id report…… = ….. “Table “Report”:: id raport I have developed al…

Hello Board, I'm trying to develop a BOM Report. I have a table of Products_Completed. Each Product has a stock code and a timestamp of when it was finished. I have a table of Products, related to Products_Completed through ::Stockcode. I have a BOM_Products table that is list Line Items. There is a BOM__Parent_Stockcode field, a BOM_Component_Stockcode field, a BOM_Quantity field and a BOM_UOM field. This is related to Products through ::Stockcode again. I am trying to get a list that shows all the BOM_Component_Stockcodes and the quantities that were used. How do i link this together? Should i start a layout based on the BOM table?

Hello there fellow Filemakers! Â I have recently started to develop a Filemaker solution for the company I work at. I instantly fell in love with Filemaker and I'm looking forward to continue using it. Â To my problem: Â We're currently trying to put together a script that can parse a JSON feed with our orders we get from our webshops API and I have managed get all the info I want except one thing. Every order has a order-number and I have managed to get this info and match it with the customer info form the feed. Â So what I want to do is to export every SKU with the related order-number to another table so that I can use these in a portal for my order and invoice-la…

I feel self-conscious asking this rudimentary question after I've been using the product for years . . . but I guess I've never needed to do this before. I'm sure there's a tutorial out there and would appreciate a pointer to it. Googling combinations of my question hasn't turned up the answer. I've always done one-to-many relationships with fixed 'categories.' First, we create the types of things we're doing at the library (programs for kids, programs for adults), then we create the entries that fall under those types (Story time class and finger painting under programs for kids, computer class and Guest lecture under programs for adults). Same model for P.O.s we iss…

Hello, I'm trying to build a database for months now and I still can't make it work. Here is the description: There are two different dimensions: 1. Funds, Subfunds, Sections, Subsections, Series and Subseries: these are categories through which the documents have to be hierarchically organized. A document will always belong to a Fund, but it might not have a Section, only a Serie, for example, but it always follows an hierarchy: Fund -----> Subfund -----> Section -----> Document Fund -----> Section -----> Document Fund -----> Section -----> Serie -----> Subserie -----> Document 2. Installation Unit, Compound Document, Si…

Hope someone can help. I have a number of related tables. Companies Company_addresses Personnel Personnel_addresses Companies relates to Personnel and Company_addresses Personnel relates to Companies and Personnel_addresses Company_addresses relates to Personnel_addresses (sort of) The intention is to have unlimited addresses for both companies and the personnel related to those companies In the companies' form layout I have a portal that shows the related personnel. This relationship is based on a company_ID key. In the companies' form layout I have a portal that shows address fields. This relationship is based on a multikey drop-down global fiel…

I know I must just have a "designers block" on this one. I have done similar things in DB2, Oracle, and MSSQL. But in all these cases the language I was working in had multidimensional Arrays I could use to solve the problem. Problem: Create a vertical table with the flexibility of growing the width or depth of the table over time. Displayed table is something like this: |2010 | 2011 |2012 |2013 | Net Change between last two Years Measurer |Intern|Extern|Extern|Intern| Factor 1 |3.5 |3.6 |3.4 |3.5 | 0.1 Factor 2 |2.7 |2.6 |2.6 |2.6 | 0.0 ...... Factor 10 |4.0 |3.9 |4.3 |4.1 |-0.2 Weighted--…

I need to setup relationship where I can add up multiple number of units to give packages...and multiple packages to boxes all for a given year for eg. Years Units Packages Boxes 2013 x11 =2 x= 5 17 2013 x12 =3 2013 y11=6 2013 y12 =6 y=12 2014 x11 =2 x=5 15 2014 x12 =4 2014 y11=5 y=10 2014 y12 =5 I am not sure how to set up such complex relationships and 'sum…

Hi All, Is there a way to get a lookup to go to the last matching record instead of the first. I have a table of goods received with many of the products not having a serial number. Where the product has a serial number it is not a problem as the relationship is defined by both the part number and the serial number but without a serial the lookup will keep finding the first created part and Stock ID number rather than the the stock that is probably being used which will be the last one received

I know this is probably a very easy solution, but I'm still very new to this whole relationship process. I am hoping someone here will be able to help me. I have started with the 'Contacts' starter solution from FMPro11. In it, there is a tab feature called "Related Contacts". In this, there is a portal that shows other contacts with a similar feature. The deciding feature is selected using a radio button tool. I am creating an alumni database for both current and former students. I would like to be able to show similar contacts in the portal based on a feature that I select. The starter solution comes with this set for "Name", "Company", and "City" but I need to expa…

First let me say thanks for any help and/or Guidance on this issue. I'm in the process of updating a very old Database for out company. We use this Database to know what Jobs are in the shop and their History. Currently it's a single flat database. We have 2 main fields for this issue. Job # - Text Field History - Text Field What users do now is when a new job comes in that is like a previous job they just update that record with new info.. The reason is so they can keep the history info. The History info is a manually entered... Used for Notes about the job. What I'm wanting to do is get them to stop overwriting the Records so that at the end of the day, …

Hello, Can anyone point out what I am missing. Solution is a Contact Manager. I have a simple one table solution. I have a 100 Records. Some of the records are related and I simply need to be able to have a drop down listing all names. The user needs to be able to link as many names he wants by selcting them from a portal. I created a self join table using the primary key. I linked them (it gave me a 1-1 relationship but I dont see a way to make it one to many...) I then created a portal selecting records from the self joined table. Now, when I create a new record it enters the full name into the first row of the portal. …