Relationships

Hello Everyone,  I hope i have posted this in the correct place? I am creating a database to manage our burial ground, at present, all records are paper based but due to the success of the ground, there is becoming a real need to have everything in a good working database.  I got of to what i thought was a good start however i have now got totally stuck!!, I have attached a copy of the relationship graph to try to help explain where I am with it. When a client comes to us, they can do any of the following: 1) Purchase a plot outright - This gives them a "Pre Purchase Certificate" 2) If they have purchased one plot outright, they may reserve a second for …

Hi, all: I'm trying to wrap my head around the schema set-up for the attached checklist so I can keep track of a solution's "to do / done" task list. The Task list will be the same for every layout to be reviewed, so with each new record (per layout name) in that list needs to automatically appear with respective Date and check boxes to be ticked when the task is completed. I thinking that it's a simple parent-child-portal set-up, but how to auto-create all the tasks? If I can get some input as to the schema set-up then I can do the rest. Thanks in advance for your help! JCC Checklist.pdf

Hi folks, I'm in the early stages of doing a database for my music dept at school. I ran out of ideas when planning and started to write, hoping inspiration would come to me, but it hasn't. I've done the easy bit first and have a table of pupils and one of classes, with a join table and a portal in each. I can so who's in each class and which classes pupils are in. It's adding assessments that has stumped me. I have a third table of assessments with a join table to classes and another join table to pupils. What I can't work out is how to create a layout where I can create a new assessment (or add an existing one) to a class so I can give the pupils grades. I'…

I am using fm12 04 advanced.  I have Membership application. There is a Membership table. On the main layout, (based on Membership) I put a portal on the left with a MembershipTOC called MembershipPortal. MebershipPortal is related to Membership by a X between PK_MemberID and PK_MemberID.   The problem: When I run this little 'add new record script' it adds a new record BUT, it also assigns (somehow) that new record number to an existing record; thereby cross linking them. Huh?   I can't see anything wrong. Can you? Thanks for your help.  Perplexed Ron

To make my request simple, suppose I have multiple children records for each parent record. In each child record there is a number. I want to add up that number from each of the children records for each parent and put that total in the parents record. I know you can have summaries on a report, but how do you get the total into the parents record? As an example, if each child record contains the amount of a tuition payment for a particular family. There are a number of these payments during the year. I want to total that amount for the year and put that amount into the parent record as total tuition paid by each family for the year. Make sense? Help?

Hi all Been trying to figure on how to do this for a couple months now and as you can see I am not to good on relationship. I attached three jpeg file that I hope explain what I want to accomplish. After verifing that a welder has welded in a welding process that he has been quialified for, I log the date in a continunity file. This need is done for ever six months of employment. The file continunity example show how each individual record is recorded in the file. The continunity have example is how is print on the form that is posted in the foreman office. The continunity want is how I would like it to print. Thanks in advance.  Lionel  Â

I am in the process of creating a parts inventory system. I have most of my database done and the system is coming together well. I have a barcode scanning system in place so that my technicians don't have to do anything other than scan a part as they leave my shop. However, I am running into a problem with the way I have Relationships and Lookups established and I am hoping I am overlooking something simple. My database consists of 2 main files (Inventory/parts database and Transaction database). The Transaction database references the Inventory/parts database based on a scanned barcode; every part in the database has a manufacturer/part number/barcode associated wit…

I have a db with many records about available cars. Now I need to add history records to each record in db (I feel it may be like creating different table for each initial record). So, I want to have a "history" button and it should open a new layout with a new table (for each record in initial db) and 2 fields: date, comment. Is it possible to create a new table for each record via script? (is it necessary to do so?)

Good afternoon all, Really hoping someone can point me as to where I am going wrong on the attached file? [Layout: CookingRecord] I can make my portal display the data required, but the user interface is not what I need. Upon clicking in the first field ("Product ID") a drop-down list displays showing all the related data from the table ("PRODUCT"). What in fact I want to do is filter these lists in a much better way. What I need is: a user to click in the field ("Product Type") in the portal (this displays a drop-down list of all the records in the related table) when a selection is made the next field in the portal ("Product Specific") displays a drop-d…

Hi everyone, I hope someone can understand what I need help with. So I pretty much got the idea of how to create fields and assigns meaning to them. I created all the fields that I needed under the project/task fields, and then I started to fill in clients info and the tasks. However I noticed that the project/task are not directly connected to the client. Meaning if i was last at customer number 1, and project/task #1 and then I switch to another client when I click to the project/task it's still on #1 wchich is not for this client. So what happens now I have to manually scroll through project/tasks to find the right one accordingly to my clients. It's okay if I on…

I made a database, yesterday, I was using it today, then I closed it, but hours later, when I tried to open FileMaker Again, appear some problems: first, I made one record (Record 1), but when I tried to make any onther record (Record 2), it cames with the same data from first record, and if i modify it, the data changes to Record 1 too. And hours later, I can not type any data into fields, what's the fail ? I have not any global field. Im with full acces privileges.

I have a schedule of rooms which are used at different times of the week (acticvity) (for a total duration). The information i have has a start week and an end week. Each activity might only go for 4 weeks in a year. The end result to to establish how many hours each room is used each week. (ultimately in a chart). I related the start and end weeks to a new table (52 records for the weeks). That worked but didn't help my weekly totals.

Forgive me if I'm wrong but I was under the impression that when using the LIst(field) function to combine multiple records into one field, that the sort order was meant to be defined by the sort order set in the relationship. I've had this working before, but for some reason it doesn't seem to want to do it anymore, and is always reverting back to the order of when the records were generated. I've attached an example here, where i eneterd the data in this order: ONE, THREE, FIVE, TWO, FOUR (with each record having a number field corrsponding to the word to use to sort on: 1,3,5,2,4). As youcan see the portal sorts correctly, but the COMBINE (List) field which…

Here's a curly one for the FM gurus out there. I'm not bad at FM but It's only a hobby for me at this stage. The situation: 1. I have a db of postcodes in a single table. Each postcode location has geo-location latitude and longitude fields that are retrieved via a script from Google's API. (This only happens on data entry.) 2. I have a dynamically filtered portal called related postal codes. The standard trick here. Set up a relationship using a related_by global field with radio button selections that generate a result in a related_key 'Case' statement to filter the portal by relating with a related_multikey calculation field that contains a list of the selection…

I have a membership database where there are Visitors.  The relationship is as below:  Below is the record for Frank Abashier. When I select his name in the meeting portal, the city and state are 'brought over' from his Visitor Record and show in the portal.   When I make a 'new' record in Visitors for IM NewGuy and give him a city and state, and then go back to the Meeting portal and select his name on the Visitor Side, his City and State information DOES NOT come across. ? Huh?   The Meeting Layout shows Members on the left (it worksw) and Visitors on the right. Both shown in portals. The layout is based on Meetings. The Visi…

Hello everyone down here, permit me start this way please i am a school teacher ( in Africa), new to filemaker and starting with a difficult database. It goes like this: a new ACADEMIC YEAR began the ACADEMIC YEAR is divided into three TERMS( called 1st term,2nd term and 3rd term) and STUDENTS are registered in different CLASSES (called primary1,primary2,primary3 ...........) all these CLASSES offers similar SUBJECTS ( maths,english,health science.............) in each TERM three types of EXAMINATION ( 1st test, 2nd test, 3rd test) are conducted in each SUBJECT,their scores added together and FINAL GRADE produced for each student in each class(report card is…

I am developing a solution with FMPA12 on a Mac and I am working on the project with another developer that is working on Windows. He is complaining that he us unable work with long table occurrence names becuase he cannot see the full name in certain FileMaker dialog windows. (please see attached example) Â So is this a FileMaker issue or a Windows limitation? Â Other than shortening the table occurrence names is there any other solution to this problem, such as an OS utility that lets you re-size this type of dialog window? Â Â

i'm trying to create a database for my small business. there are 3 tables in this database for now. invoices payments invoices_payments (join table) i have created a relation between them through a join table and it is working nicely till here. now what i want to add is a new table for customers and allocate the payments manually as some of my customers send the payments together for few invoices and it might not be final amount so how shall i allocate this amount? for example if payment received from a customer for 3 invoices together then i will mention the amount received only once and there will be a portal showing the pending invoices where i can alloca…

I used one of the forum threads to get the count of a filtered portal. - create a summary field that has the count of PKID - duplicate the portal - set to a single row portal and put summary field in - this works great. and I know that an empty row shows up in portals because of the relationship. I cannot figure out how to create a calculation field based on the portal count. example: I have (2) filtered portals in a layout that filter items based on a due date. If it is late it appears in portal 2, on time it appears in portal 1. I duplicated portal 2 (and turned it into a single row and included the count field). It shows the count correctly based o…

I need help designing an invoicing solution that is slightly different from the standard one. 1. The INVOICES table is in a many-to-many relationship with the PRODUCT_GROUPS table. Each invoice contains multiple product groups, and each product group can be on multiple invoices. 2. The PRODUCT_GROUPS table is in a many-to-many relationship with the PRODUCTS table. Each product group contains multiple products, and each product can be in multiple product groups. In my mind, I envision something like the following during data entry: A. The user visits a layout and creates a new invoice with multiple product groups on it. B. The user then visits a second invoice …

Good Day, I am trying to add several records from one table in another record, in another table. This DB is based on a process that has two steps. From Step 1 to step 2, some records need to be merged. Table1 is 50 records, one field in particular is ItemID, one is ItemAmount. On Table 2, I have 0 records and I have a some fields similar to Table1, so I am populating them with lookup. The relationship is based on ItemID. So far so good. But some records on Table1 now need to be merged to one record in Table2, in particular, the ItemAmounts need to be summed to generate Table2::ItemAmount... I want the user populating Table 2 to specify the ItemID's f…

I'm building a solution which will hold millions of records on about 3 or four tables. 2 of those tables will have fields that hold "static" data that should never change once its created. Then there will be a number of fields on those 2 tables which should be constantly changing (classification, performance and quality checks for "static" data). Would it make sense, specially from a backup point of view, to separate those fields into one to one tables?

Dear Members I 'm a brazilian doctor newbie in filemaker, nevertheless I'm trying to develop a small system ( EMR - electronic medical records ) to be used in a social/caritative public clinic environment. So, the problem is as follows: a database with the main entity patients each patient has n ( unlimited ) visits ( or consultations) each consultation has n ( unlimited ) prescriptions each consultation has n ( unlimited ) exams and etc Thus, I built one table patient ( for demographics) linked to table_visits. A script to generate a new patient and a new visit which are displayed in a portal ( see attached file, please) My problem is to link in…

This has been stumping me for a long time, and I am sure I am overlooking something incredibly simple. Hopefully I can explain this well enough.  I have a relationship between several tables (see example ).  Whenever a record is created in any of the three related tables (Table1, Table2, Table3), I want a new ExtraData record to be created with the same key as the record in the related table. I also want the userValue to be set to the value of a global variable I have $$curUserValue, only if the ExtraData::userValue is empty. If the ExtraData record exists, the ExtraData::userValue changes, AND the key changes, the ExtraData::userValue should just be updated. …

I created a database with a few layouts and one table off of a youtube tutorial and when I tried to customize it a little bit more, I inadvertantly screwed up the relationship between one of my layouts and the table that should go with it. My goal is to have a database for the horses I am training with a tab for the table of training entries I make pertaining to a given horse. I initially created a HorseIDFK field and related it back to the HorseID from the original layout. I wanted to make it so that from the Training table layout, instead of using the ID #, the table used the horse's name but now I've really messed it up. I still have all my training entries, but th…

My database consists of the tables: Projects (_pk_project_id), Employees (_pk_Employee_id), Clients (_pk_client_id) Clients_Individual (_pk_person_serial) and Protocol (_pk_serial) (where each document that enters or exits the office is tabulated with a serial no.) I have joint table for Projects and Employees (many to many), and also clients has a relationship, is the father of, protocol. Now when I try to relate projects, _pk_project_id to _fk_project_id in the Protocol table, I get the "there cannot be more than one relational path between any two…" error. I suppose this is because Protocol is already related to Projects via Clients. And then it prompts me for a n…

I have a table with a list of the employees of a company, and almost each one of them has an account with certain privileges in fm. How do I associate each account with a record in the table, so a modified by field can be inputted with a Name and Surname field from the employees table?

Hi Everyone, this is my first post here, but it seems this community is really dedicated to providing helpful feedback. I thought you might be able to help me with my problem. Â I am helping my employer move from his old database (FoxBase) to a new filemaker system. I managed to extract all of the data and structure of the old database, normalize it (to some extent) and have re-created this in FileMaker and successfully imported all the data. Â That was the easy part. I have a bit of experience with php/mysql so perhaps my understanding of retrieving data is a bit different from how filemaker treats it, but I cannot for the life of me figure this one out. Â I am …

Hello, I am new to FileMaker, and took a class about databases in college. I know some sql from that course. Does anyone know of a good resource that explains Relationships in the context of sql (and E-R Diagrams?), or even FileMaker in comparison to a to a basic database?

I am making a very simple database to track residents and their co-signers and their payments for the apartment building that I run. Since it is possible for two or more people to have the same first and last names, I hope to create a compound key in the 'people' table that uses the first name, last name, plus a date to come up with a unique identifier that will only apply to the person in each record. Anyone that can tell me how to do this in FMP 11 is invited to coffee and breakfast and/or sushi in Berkeley, California, or....(you tell me what you want)?

I'm completely baffled by this. The attached file contains two tables (Parent and Child). Parent has three fields, Parent::Serial, Parent::DefiningProducts, and Parent::DefiningResources. Child also has three fields, Child::Serial, Child::Products, and Child::Resources. The relationship is a simple one: Parent::DefiningResources = Child::Resources AND Parent::DefiningProducts = Child::Products. If you look at the portals, you will find that the relationship does not seem to be matching the expected records. In particular: the Parent record with serial 287 is correctly matching Child records with serials 3347, 3905 and 4086, but is also incorrectly matching…

Well not easy to word in English..!! I have a list of persons, of which I consider the head of the family (generally the father) that is entered with name surname, d.o.b, record ID etc.. on the same layout I have a portal in which I want to enter the rest of the family (wife, children, etc living under the same roof) I want these father and family members be in one single list of persons, and of course related (by creation in the portal) when I need to treat them as family. My problem is that a portal is for related records, and I turned the problem in many ways, but got kind of brain exhaustion...!! Any lead will be appreciated. Also accepting slaps..

In y'all's opinion, what record should carry the discount info, e.i. discount %, discounted amount, etc... the Invoice record or the LineItem record? I figure I have three options: 1) Include the discount info in the LineItem 2) Create a LineItem as the discount 3) Not discount the LineItem, discount info in the Invoice record I do create proposals > milestones and then copy into invoices > items I use the proposal area to build estimates, show my potential cost and net, etc... I currently have the discount info in the Milestone, this way I can see my net profit on ea. line when building an estimate. BUT... maybe I should NOT handle the same way o…

Hello - I'm a little stuck editing a relationship structure and wonder if I'm on the right track... I've built an invoicing database for my friend's therapy practice with tables for the following: Patients, Invoices, Appointments, Codes The invoice body is a portal to the "Appointments" table. Each appointment includes a date and procedure code, and the description and rate for that code are lookups in the "Codes" table. I would like certain patients to pay different rates for the same codes. I created a "Custom Codes" table which includes the account number, and made two table entries for it in Relationships: one joining the account number to the "Patient…

Hi all, I need to filter records based on Product availability. I have a table, Products. There are thousands of records, each having different product code in it. Also, I have a table, Project. When i create a project & i use different products in it. Suppose I created a Project ABC for the duration 10/6/2013 to 15/6/2013. Between these duration, I will get a list of available products during these period. and from that i will choose the product which i require. i choose a Product X for this duration. Again, when i create a Project XYZ for the duration 12/6/2013 to 14/6/2013. Product X should not be available in the list. Please, give a logic or…

I posted a sample file to explain what i'm trying to do. In the employee record i want the field flag to have a value of "on" if any of the related records does not have a value in the stop field. This needs to work even if the portal is not on the layout. thanks trying to figure out how to post the file Untitled.fmp12.zip

I 'm setting up a database where one of the main tables (entities) is one called projects. This table involves a project number and related information including a client for the project and employees involved in each project. I 'd like to keep separate tables for our clientele and our employees. And of course use their data to populate the projects table. And I 've really confused myself as to how to relate these tables with keys. Each project can have one client, and more than one employees. Could I just keep them unrelated and populate the projects table via lists? There's also another table for salaries where each employee will get a % involvement in each …

Perhaps it's because I'm working too late...but I'm stuck on a relationship design of which I'm sure I've done something similar, but I just can't seem to wrap my head around this one tonight. :-( Â If anyone can help me solve this one, I'd certainly appreciate it. Â Please take a look at the attached relationship graph to quickly explain my schema (it's the first image below). Â In summary, I have four tables, a table with a list of features in all stores, a join table joining the feature records with store records. Â In the join table, there is a count field where I store the total count of that particular feature in that one particular store. Â And finally I h…

Hi Everybody! Running FM 12 on a mixed network. I've discovered a really strange anomaly. In a database with 200 grandparent records, 20,000 parent records, and 0 children records, Filemaker keeps automatically creating a child record associated with the first parent in the alphabetically-sorted group of parents. When I delete the child, Filemaker automatically re-creates it! The way I discovered this was as follows: I have a layout which displays a portal. The layout is set to show the children. The portal is set to show only those children with a certain status. This portal is established by way of a self-relationship between a global field within t…

Hello everybody. I'm designing a really simple little database to manage communication campaigns per town. It has a clients table and campaigns table. I am trying to show the campaigns for the same town but different clients, and the catch is I do not want the expired campaigns to show up. So I created a relationship using the town and a global date field (with current date automatically inserted) to keep only the the campaigns for the same town and whose date is after today. At least that is how I thought it would work, however it does not. All of the campaigns for the town show in the external table, regardless of their date. The global field and the …

I have a table (table1) that is related to another table (table2) by its key. When a record is created in table1, a new record is created in table2. This is defined by their relationship. I want the new record in table2 to have some checkbox fields auto-populated.I have tried using the auto-fill option for the specified fields in the related table, but it is not working. The only thing that seems to work is a script with some "Set Field" calls to those fields, but I have only found an "onRecordCommit" script trigger to run this, and I need to to run as soon as the record is created. Is there a better way to do this? Thanks.

Hi all, Was hoping I could be pointed in the right direction here - hopefully I can explain myself fully as to what I want to achieve... TASK: A user has many fridges/freezers in their kitchen, they need to record the temperatures of their fridges twice daily. [FRIDGE NAME TABLE] To set-up their database, a user first must create a new fridge record and name/number each one, and select range of temperatures the fridge could be running at when the records are taken, For instance: "Fridge 01" temperature zone could be "1°C", "2°C", "3°C", "4°C" "Freezer 02" temperature zone could be "-24°C", "-23°C", "-22°C", "-21°C" I don't want the user to have a lon…

I'm a newb. and am stuck trying to figure out how to set up (change, really) our current donation tracking system. We have a Contacts table (1 record per person), a Donation Tracking table (1 record per donation) and a Company table (1 record per company). Each Contact can have many donations. Many Contacts can be with 1 company. The problem is that when a Company donation (meaning the company wrote a check) comes in, I record it in under an individual Contact at that company. If I don't choose the same Contact each time then I could (do) have several Company donations distributed among several contacts and I'd rather they be connected to the Company, so I can get summari…

I recently read about the Party Model (and looked at Daniel Shanahan's example at http://www.newleafdata.com/downloads.php), which has separate tables for People and Organizations that are related to a Party table. I've been thinking about how to create a compound or multi party model, but I'm not sure how to proceed. This would be for a database that catalogues deeds, mortgages, and other legal documents on file in various county archives. These documents typically have one or more grantors and one or more grantees. The current version of the database has a People table and a Documents table, and records are related via join tables (one for Grantors and one for Grant…

Hi, I've got a central title database set up for a publishing house. They have promotional pricing that they need to schedule by date. I have a promotional pricing table set up with a start date, an end date, and the promotional price. A calculation field on this table determines whether the particular row on the table is 'active' i.e. should have pricing enabled. I've got a relationship to this table with the main products table matching the ProductID and the Active field. I would have thought this would work, but I'm having trouble on a number of levels and have tried a whole bunch of different combinations but it still doesn't seem to update day-to-day. …

Suppose I have a table with a self-relation such that each record is related to the prior record (e.g. the one with a serial number one lower), call this relationship : "SelfPriorRecord" What happens if I make a calculated field which is defined as cCalcField = SelfPriorRecord::cCalcField + 1 To evaluate this, filemaker would have to figure out the result of the prior record's value, which means it has to look at the prior record's prior record's value, which requires the record that is prior to the prior to the prior record, etc. Does FileMaker have any rules about how many layers deep it will search in such a calculation, if at all?

I can't see the wood for the trees on this one so this may be a stupid question. I want to have the option of entering an item by either its ItemCode or ItemName. The code is a 12 digit number scanned in, with the ItemName then auto-entered by a lookup. However if for some reason there is a problem I'd like to be able to select the item from a drop down of names (rather than manually entering the long serial number) and have that populate the ItemCode field. There may be a better way than lookups but is it possible to have the function I have outlined? Just to be difficult, for the moment I need to be able to make it function on FMP 8.5. Thanks.

Will try my best to explain what I need, and hopefully someone out there can help me determine the table schema to support it. Imagine there are three tables: ANIMAL, PLANT, DEFINITION Assume ANIMAL and PLANT must remain as two separate tables and cannot be combined into one. Each record in ANIMAL may have one or more DEFINITION records. Each record in PLANT may have one or more DEFINITION records. USER SCENARIO User creates a new record in ANIMAL; Users enters the word "HORSE" in the ANIMAL TYPE field on the record; User creates a DEFINITION in the definition portal on ANIMAL layout by typing a few words; User creates a second DEFINITION in the definiti…

I'm working in an environment where a large database is on the server and accessed by many. When I log in to FileMaker via an account that has admin rights, I can get into Layout view, but when I bring up the Manage Database window, that should diplay Tables | Fields | Relationships, I can see the relationships but the Table and Fields views are empty? I've looked at different related files and they are the same. I'm not familiar with this environment so can anyone tell me what is going on? Filemaker 11 Advanced. The related tables are seperate files. Thanks

Hello! Just migrated from FM6 to 12 and am enjoying all the power and flexibility! I have two tables (Materials and Restrictions) that are "joined" in a many to many relationship through a third table (MatRest_JOIN). The primary table contains information on organic materials, the second table is a listing of all the possible restrictions a material might have. That middle, joined table which allows the many to many relationship can be understood as... 1. A material can have one or many restrictions 2. A restriction could apply to one or many materials at one time 3. MatRest_JOIN table has an index (ID) of it's own records and then for each record has the fi…

Hey, I have 4 tables, Contacts, Products, Lineitem, Orders. I have a relationship that looks like this "contacts-order-lineitem-products".. so I can see the products ordered by the contacts. But I also want to see How many of the products they have ordered. Like a summary that shows how many of the products they have bought, So when I open a customer post I want to see what product they order the most. And then sort the portal by that field, So the first post in the portal shows what they have bought the most. I would really appreciate help!

I have a Customer DB that relates to another Product DB that has the following fields Product Descripton Product Code Unit Price Product Category I have a layout on the Customer DB that contains 30 fields for each of the above. (30 rows in the propsal layout) This data then appears on a secondary layout or contract page. I would like to have a third layout display products that are on the contract page if and only if they have a specific product category. I would like to do this without creating a great number of fields. Please help.

Ok, Im really lost..... I'm trying to understand why when I concatenate multiple fields into one single field using a calculation field, The field data on my layout doesn't show just a single line of text... Here's my Calculation Field: TextA &" "& B::TextB &" " & C::TextC and a REAL simple Related DB is Attached to review Thank You -sr Relationships.fmp12.zip

Hello, I need help for sorting out properly in two file attached: 1) For JOURNAL CAISSE, I would like just to sort by Imputation on my list as Date or Statut de paiement. I'm having trouble to insert a "Sort List Key" to sort properly because the field imputation comes from another table. 2) For SECRETARIAT ADMINISTRATIF - Test, I need help to sort properly in Expedition Courriers's table. Fields "Date signature lettre" and "Agent designé" are not sorting properly and I'm having trouble to fix it. Thanks in advance for your help. Help sorting (tris).zip

I'm trying to generate a report that will return all students in all grades summarized by each year. see attached file thanks

Hi everyone, Â I'm pretty new to FileMaker but I've had quite a bit of experience designing databases for MySQL and web applications. So I've got a pretty good handle of how to design a database and define relationships, but this one has been throwing me for a loop. Â I've been trying to figure out for ages how to streamline data entry for the company that my husband and I own, I was working on a web solution and then found FileMaker and realized that, if I can get it set up right, it will do exactly what we need. Â Our business works like so (it's so unlike the traditional "Customer orders products 1, 2, and 3 and we invoice them for those" that I feel I have to…

This may have been asked before around here, but I can't find anything, so advance apologies just in case! I have a parent table with dates, pointing to a child table with things that occurred on that date. I'd like to compare a field in the child table to the field of another child record, based on the preceding date in the parent table. Can I create a relationship between these tables that returns only a single match? The only thing I can think of would be another field in the child table with a lookup just for that purpose. Seems not so elegant. Thanks, Stefan

Hi, Simplified: i try to link 2 tables based on 3 fields, one of which is an un-storable calculated field (in the right side of the relationship). When i visualize this relationship in a portal (of Table2) on a layout based on Table1, i get nothing. Trial and error revealed that Field3 (the calculated field in Table2) causes the problem. I read somewhere that in a relationship, the right-part of the relation needs to be indexed (or indexable). FM however doesn't allow me to store the calculated field in Table2 as it uses references to other TOs in its calculation. (is indexable = storable?). I think i'm conceptionally doing something wrong or there is…

So I am working on a database that is very complex. One issue I am having is with one of the complex relationships. I work in the claims adjusting field so lets say I have a house that had a fire. I would create a record for this particular claim. This house would have several rooms in it. This claim would also have several different parts of the claim that would need to store information pertaining to those same rooms. So the "Claim" record would be the parent to the Rooms/Areas but also to the different parts of the claim such as the Building and the Personal Property. Each Room/Area would have several items that would go under the Personal Property part of the claim, a…

Hi Team, This one is doing my head in a little and I am stumped for the best approach. So from the start.... I have a large Performer Health DB that I am making currently and it is based on an older access DB, the main function is to track injuries and treatments of those injuries. Each Performer has a record, related to that are each performers Injury records, related to that are the individual treatments for those injuries. Performers > Injury > Treatment. When a performer has an injury they are treated and the treatment record is updated with a status that is either Restricted, Available or unavailable depending on the seriousness of injury. …

Hi all I have a system that involves one central set of dbs and 3 external sites. The sets of dbs in the remote sites have the dbs in the same names. The central system regularly updates them and retrieves data off them etc by initially opening one file from that remote set on the corresponding IP address and from that file it opens all related and relevant dbs within that site to start the process. Until recently we had a vpn using vpn gateway routers but they were old and slow and we decided to replace them with a software based vpn setup using hamachi. vpn works ok. The problem is that filemaker on the central computer that connects to the remote sites keep…

Two currently separate DB's, "Client Activity" and the "Checkbook." Activity DB has "Main Table" with client#, name, address, etc. Its related daughter table has charges and payments, and dates for those. Checkbook DB is a simple flat DB where we print checks and record payments. Right now the payments are entered twice, into the Activity DB and then into the Checkbook DB. Of course I want to avoid the double entry. When a payment is recorded into the "Client Activity" daughter table, how to simultaneously create a create a deposit record in the Checkbook DB? Second problem: The work for the CHARGES is done by 3rd parties, so when a Charge is create…

Hello I have 2 tables that named Discs and Users. How to make relationship between the tables that One User receive One Disc, AND the same disc not available for all users (Only available for One User) -(e.g. Circulation Desk at Libraries)?

What is the advantanges or disadvantages of using ID files as a Numeral or a Text. I am seeing both being used and since I am brand new to relational databases using id _kp and _kf fields I am trying to get started right. Thanks. I did search the forum for this info and didn't find it. It seems to work either way in my experiments. Mark Jones

Hello All! I have a DB that has several files. Each of these files has only one table. For the sake of good design ( I'm fairly new to FM but not to DB design concept) I want to import each file into the main File as a table but not lose the relationships. What I have tried is to FILE/IMPORT RECORDS/FILE as table and join the new table to the appropriate keys in the Relationshp diagram. The problem is that the fields in the corresponding layouts still point to the original file. If I delete the original file and rename the newly imported table to the original file name, the links are still broken. Is the only way to accomplish the end result to re-link all …

Let's say I have a table: Table A And two different Table Occurrences (TOs) that refer to this base table: T1 T2 Now, if I have a layout that shows records from T1, is there any easy way to transfer this found set to another layout that is based on T2, without creating an explicit relationship between T1 and T2? If I just use "Go To Layout" to switch between them, each one is keeping its own found set. Help? I thought I was being clever by creating new TOs to keep my relationships graph simple, but I'm now finding that I designed myself into a corner.

I have two files (among others) in my database: 'Users' and 'Questions'. Users has one entry per user. One of the fields is a timestamp with the last date/time the user logged on. Questions has one entry for every question asked in a forum. It has a timestamp field that indicates when the question was asked. I want to be able to count the number of questions that were asked for each user since that user last logged on. I set up a new calculated field in Users with the following: If( Questions::InsertedDate>PresentLoginDate; Count ( Questions::InsertedDate );"") My logic is to count the number of instances of InsertedDate in Questions where the…

I got bit yesterday and today by a basic issue. I was using the Data Viewer to write and test ExecuteSQL. The context was an old part of a solution I had started in 2006. I was having trouble with JOIN. I checked syntax and could find no errors but constantly had "?" returned. I knew the problem was related to the JOIN because the queries worked fine without it. Well, I'm embarrassed to admit the problem was this. The parent table had an auto-enter serial PK type number. All the child tables had an FK field but the type, for some reason, was Text. FM allows this as a match! Of course SQL does not. After changing all the child table FK fields to type Number everything was …

Hi all I have several related tables all hanging off a main table (filemaker pro 12). The tables are related by an auto generated unique ID. Lets call that a patient ID for want of a better description. I have merrily created these related tables while building my solution and they work fine. The patient ID can be displayed in the layouts of the related tables using the local field that "patient ID" that is related to the main table and all is good. For example one of the related tables is called drug information and my layout shows the patient ID followed by the drug information for that patient. That is until now, now when i create a new table to log wound…

I am trying to set a field in a related table, via a script, but am getting this error: "This operation cannot be performed because one or more required related records are not available and cannot be created." This is the interrelation of my tables: TableB::ID <*=1> TableA::ID <1=1> TableC::ID The script is running in a layout based on TableB (many-to-one to TableA). I am trying to have set a field in TableC ( one-to-one with TableA), so it is looking through TableA to set a field in TableC. The related records in TableC DON'T previously exist, but I have 'Create records based on this relationship' checked in the relation from TableA to TableC, ju…

I have a table called "Teams" and a table called "Games". The games table has fields like "Home Team", "Away Team", "Date", "Away Score", "Home Score", etc. What I want is in the "Teams" table to have a portal that displays all games the team has played in. I can obviously get it to do that with it showing the home team, away team, and result fields, but what I want is something like this: Say I was looking at the games for TeamA - I want an "opponent" field that says "@TeamB" if TeamA was away or "vs TeamB" if they were home. It would be great if I could have another field that says "W", "T" or "L" depending on the result. For whatever reason, I'm having issues figuring…

Hi. I'm new here. I have: table: Products field: Product ID field: Fabric (Drop down box populated with lookup from field: FabricID from table: Fabrics) table: Fabrics field: FabricID field: Products that use this Fabric (Portal that displays field: Product ID's based on relationship check Fabric = FabricID) Swell. Works ok. Problem is some products can use more than one fabric. So I set up in table: Products field: Fabric2 (same lookup of FabricID) and add Fabric2 = FabricID to the relationship. This doesn't work obviously, because the relationship now needs both Fabric and Fabric2 satisfied to display the ProductID. Is there a way to …

Just for background, I started with a template from Filemaker called “Music Library” and have now switched to “FM_Starting_Point_2_1” because it offers more of what I’ll need as things progress. I have a large collection of music Loops in the [Products] file (cuts/Loops from songs in GarageBand). These are from numerous artists and their popular songs. I have “Product” (for Artist name), “Description” (for Song Title) and “Loop Number”. The end result is; “Elvis Presley A Big Hunk of Love 3” Now for my question and it concerns the [invoice] file. In the line items portal of the solution "T12j_invoices_LINE_ITEM||id_invoice|" is set up to enter an ID numbe…

So consider this: I have a table of Clients. There's also a table of Orders and Suborders. Each order contains one or more suborders. Each suborder has a Status field the value of which can be either 'new' or 'returning': the second suborder by date 'knows' it's 'returning' thanks to a self-relation by Order ID sorted by date. I need to generate a list in the Clients layout on the fly that shows a total of "new" and "returning" for each client broken down by month starting from the current month and going back a year. So I tried to create an unstored calculation field in the Clients table that says 'new' and relate it to Suborders by client ID and that Status fie…

Go to one record, click on button and move contents in field to another record, I want to keep going back and adding multiple field contents to another record, one at a time. About six fields

Hi all, I have set up a file that lets me quickly filter out records in 3 hierarchy levels. All records are shown in portals. I like this sort of setup, because I find it quick and easy to navigate compared to the find function (at least if the amount of records isn't massive). I've attached an image of the main view which explains much better what I'm doing. So I select a main category (which highlights) in portal 1, then select a subcategory (which highlights) in portal 2, and then I see the relevant records. I want to be able to view the comments field for each type of record. It works with level 1 and level 3, but not with level 2, because I obviously have th…

Hi, I'm trying to create a lending library solution for all the sheet music at my son's music school that hopefully will include the use of a bar code scanner (although I haven't yet figured out how that would work - perhaps a subsequent post?). Attached is the database structure I've created thus far, but I'm not sure if it's sufficient. To begin with, the school only had one table (MusicLibraryMaster). My thought was to add a table for Copies (1 piece of music can have as many as 100 copies), a table for patrons (there are 50 or so students) and a table to record all the transactions called Checkout_Tran, that would have a new record created in it whenever a pat…

I was scripting a method to update some related data from the context of the parent, and then delete the parent record. But, what I discovered was the related data fields reverted when the parent was deleted. It had me stumped for while until I used a Commit Record command before deleting the parent. Now you know.

I have created a basic Contact-> Order-> Line Item <-Inventory database but am at a loss how to go about creating the next stage. Our company signs out equipment to its employees. Each Order can be made of multiple individual items from our inventory. This is basically your typical Invoice solution except we are not selling items, but rather signing them out. The trick is how to assign individual Inventory items to a “Kit number & category which then can be pulled up in an Order portal. Once that Kit number is pulled up in the portal, the line items automatically become populated with all the items associated with that specific Kit/category. Ad…

I am running 12 03 on OSX and Windows.  I have two versions of the same program; versions 999J and 999H. The main Members Layout is based on Members table.  On the left (in red) is a portal based on a TOC of Members that when selected, a GTRR script is run and in the main Members section (to the right) the correct member detail is shown. This works.  The problem: When I click a 'delete' button, in v 999H it deletes a single record.                                                                ... in v 999J the same script deletes ALL members?   So, I assume it is a problem with th…

Hi all On my inventory database i have a conditional value list for removing products from inventory. on my customer order i can choose the product then from a pop up menu i choose a batch # that is associate with the product chosen. Then i can choose a warehouse based on the batch # chosen and so on. My problem is that some products are received much more than others so therefore have many batch #'s associated with them. My pop up menu for the batch #'s is getting very long for some products. How can I filter just this one value list to only show me the batch #'s that actually have a quanity of more that 0 in stock? thanks for any help Lynn

I have a label database that creates labels based on the total number items and the number of items per package (i.e., 3200 total items, packaged 750 per box yields 4 boxes of 750 and one box of 200) I need to create a packing list summarizing; 4 boxes at 750 1 box at 200 I am not able to noodle this one out! I am able to use a 'self join' relationship which tells me I have 2 unique values (1=750, 1=200) but won't tell me that I have 4 at 750 and 1 at 200 and I am able to create a table that gives me the correct sub summary (4@750, 1@200) but I don't know how to put the sub-summary field onto a separate form. Help??

Hello, this is my first post. I have a table (Employees) that contains some employees. Another table (Qualifications) contains some qualifications. The third table (EmpQualRelate) relates the two tables mentioned above, but also includes a "ranking value". Example: John Doe, has been related to the qualification "PHP" vith the ranking "3", and "HTML" vith the ranking "1". Jane Doe, has been related to the qualification "PHP" vith the ranking "2", and "jQuery" vith the ranking "4". I have a layout for Employees records, containing a Portal with the EmpQualRelate records. Everything working nice and dandy. My problem is: I want (read: need) to be able to sort the Emp…

hi I'm fairly new to FM relationships and am looking at a design solution for a set of 4 database files set up on a network. Each database is in a separate file and each of the files holds an account for a company. My employer would like the files to auto-populate for certain fields such as address and email (shared clients) as well as connect with their respective website contact forms. I'm trying to get a grasp on the best way to proceed since the design has already been established. I'm clear on how to set up relationships within a single file but quite lost regarding multiple file situation. I don't want to propose to my employer that they combine files for eas…

Hi all,  I am after some help with summarizing some data using the attached relationship structure.   I need to show a summary of revenue per product on the Customer table and would prefer to show this as a bar chart. The problem I am having is summarizing each unique product type; I can show an overall summary for all products but I need a summary per individual product type (there are 54!!!)  I've seen this done with separate TOs for each Product Type but with 54 and the possibility of more in the future I'm looking for a scalable solution.  Anyone got any bright ideas?  Many thanks,  Juergen     Â

For the past couple years, I've been helping a local film festival non-profit with the filemaker database that they use to track entrants. The original design was not optimal, because it had all the data run together for all years, when it needed to be separated by year. The number of the year also happens to be in the filename. So when each new year's festival has rollled around, I've helped them rename the files, re-create relationships, fix script references, etc. The last Filemaker version I had worked with was 5.0, but I had been able to muddle through this process for 2011 and 2012. However this year, I've had difficulty doing it for 2013. I renamed the files an…

Hi...Hope I am putting this in the right category. I need to set different pricing for each product in my product table. For example, each product as a different price depending on the location it is sold. The end user would have to be able to update these prices without changing any prior records. I am a little stumped regarding how to set this up. Any information to lead me in the right direction would be greatly appreciated. Thanks!

Hello, first of all I apologize if I am not posting in the right place but I'm kinda lost and my English is not very good I'm trying to make a database for recording projects, these projects can be of various clients. I could not develop until want to develop more Here's what I wanted to do A company may have multiple departments (eg markting, sales, purchases), have the project and its data, referring to stock products that can be sold in this work and a description free. The best way I found to do this, do not know whether or not compliquei with table customers - cliente Departments - departamento jobs - trabalhos Job data - dados do trabalho products - produ…

Hi, I have a parent table called Subs Table and this table has three child tables, Sub1 data, Sub 2 data and Sub 3 data. Each of these child tables has a field "Year" defined as a number field with auto enter Year(Production Info::Payday). The layout based on the parent has three one-line portals. One for each child table. Each child table has identical relationships to the parent. The Year field works fine on the second and third child tables, but not in the case of Sub 1data. Oddly, if I delete the portal row (related record) and re-enter the data, it does work! Also, Production Info table is far removed but with proper relationship chain, and the auto-enter does …

My CRM database tracks mailshots sent out to prospects via a parent/child relationship. I have a portal on the "Prospect" (parent) record layout, which displays the "Mailshot" (child) records linked to the prospect. I'd now like to create a child record for a group of prospects all at once. I tried to do it by creating a found set and using Records > Replace Field Contents. But it doesn't create a child for all the prospects. Is there a way to do what I want it to, or am I going to have to rethink the way I've designed the CRM?

I have a bird database with 2 tables: The Species table has one record for every bird species in California. The Observations table has one record for every observation of each species. So, for example, there may be 27 observation records of the "Western Bluebird" species in 2013. I need a relationship that will give me a subset of the first observation record for each unique species. I have an existing relationship that results in a single record per species, visible from Observations table. This allows me to see a list (in a portal) of each unique species observed, but because the unique species name (1 species per record) comes from the Species table, it…

I am new to FM and am stuck on auto filling out fields. I know what I'm trying to do is possible, just don't know how. Let's say I have a list of Companies, CompanyA, CompanyB, and CompanyC. I have in my layout Company NAME: ADDRESS: CITY: STATE: ZIP: I want a drop down from Company NAME, Select that company, and have the Address, City, State and Zip fill in by itself. Do I need a table for each company then relate them somehow? I've played with it but can't get it. Can someone walk me through this? Thanks!

Hey everyone, I'm new to FM, just downloaded the trial this Monday and I have been reading the tutorials and trying to see if it's a good fit for me. I'm in a research lab where we deal with a lot of data, and a lot of it needs to reference other data. My first problem is making a solution to creating subfolders- we start with a bunch of wafers and I I have them and their info on a spreadsheet. Then we dice each of these wafers into 4, and for each group of 4 pieces we subject to different procedures. It basically turns out being like a phylogenic tree. I need a way to create all of the subfolders in FileMaker but I don't know how. And then comes the problems of…

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 …

Hello, I have 2 tables Orders and Costs. in the cost table I have 2 fields "description" and "hours" Now 1 order can have many costs ( one to many relationship ). When I create a post in the table order I want it to automatically create a post in costs and set the field "description" to the textstring "workhours" and I want the hours field to automatically update from a field in the order table, so when the field in the order table changes, then the field "hours" should change to. Now the trick is I also want to be able to create more posts in the costs table and write what I want in the field description and hours. If "hours" is a calculation field it works …

I started working Filemaker Pro in 1997 with v3. I created a database to track daily sales; which were being logged in monthly Excel worksheets. After building my solution I imported all of the data back to when the restaurant opened. I have always kept copies of work I did for employers, my personal portfolio and archive of how I did something. In my sales table there was one record per day. I created a field for SDate (sales date current day-original input). I created a field that was SDate.lag7, SDate.lagLastMonth, SDate.lagLastYear. With ver3 I created relationships between each of these and SDate. I had fields to look up food and beverage sales by the rel…

Hey Guys, Apologies if this is in the wrong forum, there are a mind boggling number of sub-forums on here ! Basically, I'm developing an events management system with a slight twist, in that events can be submitted into the database online - through a php interface. I'm happy with the php side of things, however I'm wondering the best way to go about creating an approvals process. As it stands I have a current_projects table, with around 60 different fields in, which is related to a few different tables (contacts, dates, etc), this table should only contain approved events with approved information (checked by a single member of staff). I'd like to set…

We're developing a new quoting system for our company. We have items from suppliers that we will use repeatedly in different quotes. Each item is different and their suppliers may offer one price regardless of quantity or one or more price breaks depending on quantity. Because of this, we have an "Item Price Breaks" table that allows us to add one or more price breaks for each item we sell that can be unique to each item. The main pieces of data captured in those price break records is quantity (e.g., 1-100, 101-200, etc), per (e.g., each, case, etc), and then the price. We then have a separate quotes table in which we can add additional information based on the type of…

Hi Guys. So I have two tables - "Inventory" and "Consignors". The Inventory table has one field that is called a "Secondary Asset ID" which would be the Consignors ID and the Asset ID tied together. The Consignor typically beings with an S, though that isn't always true (or the asset may not be consigned at all). A typical Consignor ID is usually three digits (i.e. S310). An example of a Secondary Asset ID would be S310-HDCR10010, or BSI-HDCR02654. In the Consignors table I have three fields - Their ID (in this example, S310), the Consignors name, and their company. Currently I have the Consignor ID field tied with the Secondary Asset ID field, with a ::consig…

I have data in two tables that share similarly structured but distinct data. Both tables have a primaryKey field that's set to be unique, as well as a name and status field that have no validation. All three fields are set to index:All. There should be no overlap in the keys between the two tables but the names and statuses can be whatever. In the join table, I have a foreignKey field that is set to be unique, and corresponding fields for name and status. When I started, I linked one of the data tables into the join table by connecting all three fields and allowing the data table to create records in the join table. This was working great until I added the second data…