Relationships

Hi all I am about to start implementing some changes to a previous database system I wrote. It is being altered to suit the needs of a different company. One of the primary tables in my solution contains many, many inventory records. In the current system, it can take up to 30 seconds to load screens that reference the inventory table which contains up to 20,000 records (I know, I know...) I seem to remember reading that FM can run more efficiently if different individual FMP files are created solely to store tables containing large quantities of records, and then have a central file connected to the files containing the records via a relationship. Can someone co…

I ran into a FM behavior today that just doesn't make sense to me. Here is some basic test structure: two tables, related based on the ID (one-to-one), and a calculation field in the related table. (This is a contrived example, but it is mimicing an actual situation I ran into today. It is intended to be highly simplified to demonstrate the behavior. ) TableA: ID, Constant1 TableB: A_ID_fk, Text_calc Text_calc = If ( TableA::Constant1 ; "Yes" ; "No" ) TableA::ID = TableB::A_ID_fk Conside a layout based in Table A, showing the ID from A, and the Text_Calc field from B. The odd behavior is that, even if there are no related records in B (or fra…

hi! Â I have a table which has Date, Amount and Source fields. I want to display the aggregated Amount for a particular Source grouped by Year. User can select the year range and we should be able to display aggregated Amount for all the years which fall under the selected range. ( Please refer attached screenshot: 'Required Report') Any suggestions on how to proceed with this? Â Thank you! Source.fmp12.zip

I am using FMP12 on a Mac (10.8.5) and tried to export data that was from a query and got the message The file “Status.fmp12” could not be opened. (Not Found). What do I do? What is that file? The database was originally created in FMP5.5 years ago but I converted it and have been using it, i just never tried to export data before. I need a list of all the email addresses in our records. Any help would be appreciated. Kathy

I believe this is a rather straight forward question, but I can't seem to wrap my head around the solution. I have a master table that is linked to 7 related tables. Each of the related tables has 1,000's of records. Many of these records have the same master link. All I want to do is determine how many master records have NO relationship inside the related table. For example . . If my master has 10 records and a related table has 900 records. In the related table lets say that there are 100 records for each of 9 master records. The answer I'm looking for is '9', which is the number of unique links between the master and related table. This information lets me det…

Hi all, Maybe I'm asking something that's been explained before, but I can't find a suitable solutions anywhere. My problem: I got a comma seperated text file with 1.4 million records. Simplified it goes like this: name location present/absent/doubtful/no data name1 germany present name1 belgium present name1 france absent name1 spain doubtful name1 italy present name1 portugal no data name1 hungary absent name2 belgium present name2 uk no data name2 ireland doubtful etc. As you can see I now have multiple records for eac…

Anybody can me explain: two tables has multi-criteria Relationship and Table 1 has indexed Global match field. I have a script with a Loop creating new related records, so 1st record will be created, but on second and next will be error 510 (Related record is empty or unavailable)

Hi Board, I have a table, that logs a Time and a Works Order number. I have another instance of that table, used for a user interface layout. It has a self join that i am using to do a count of Works Order. So, in the User Interface, there is a portal of the self join that shows all the records with the same Works Order as in the original table. I want to make a field that is a Calculation (Count) of the self joined table. But, on adding this field, i cannot select the other instance of the table because its not Related to the Original instance. What am i doing wrong here? It tells me I cannot use a non related field or that it should be a global …

Basically I have a small test db setup with three tables. The person. The report comments the persons record including report comments. I need to get the comment 2 field to autofill based on what is entered on form test1234 in comment 1. I cant seem to get it to do this, im sure its simple but....... I need to save these comments so that we can go back and reference them at any time in the next 10 years. Each year the table containing the comments will changes hence me saving them against the persons record. Any tips on making this field autofill against comments1 would be greatly appreciated. test1234.zip

Im in the process of just learning Filemaker, so please bear with me. Everytime I think I have a grasp on the process, I get humbled. So here is the latest: I am trying to create a report about baseball. My goal is to have a report by position and display all the stats for players that play that position. I have downloaded a .csm file that has every player since 1871 and their associated stats. So here is my thought process. First I created a table for Position: Position pos_name pos_id Next I created a table of players in that postition. I imported these players from a .csm file through the import records: Since I did not care about LF, CF, or RF (instead of), I cre…

Is there a way to 'loosely' relate tables? For example, matching records based on their addresses: "12-34 Maple Ave" on one record in a table to match with "34 Maple AV" on another record with a table? While the exact match is always best, sometimes the need to filter based on 'loose' criteria is important. Thank you:) L.

Hiya, Baby steps here in building an application. Have gone thru the Lynda tutorials on FM12 and followed the exercises. So am comfortable using FM. As a test build, I am working on a kindergarten db. I have identified all the fields and the required PK/FK required for relationships. My question is: 1. Do I really need multiple tables or can I cut it down to one or two tables only?? 2. If I go with multiple tables, would it be easier for a newbie like me to create each of the tables as a separate file or put all the tables within one file?? Thankz for any help/guidance. If some Guru wants to have a look at my tables, I don't mind sending across an …

I created a relationship between three tabs -- hopefully correctly -- and now I want to show two of the tables on the join table by using portals. Yet when I set it up, nothing appears? What am I doing wrong?

Just can't quite seem to get my head around this - though it will likely be a "Doh!" moment when someone comes up with advice. Scenario is as follows: I build an inventory of items I keep for camping. For each trip I do, I want to keep a track of what I plan to take/took. My initial thoughts were: 3 tables (with fields) 1. Inventory (contains fields such as Item, Category, Stored Location, Packed Location) 2. Trip (Trip name, Date, Duration, Season, Expected weather, Actual Weather) 3. Packlist (To Pack?, To Buy?, Packed?) The way I want it to function is this: I build up an inventory of camping equipment. I create a new trip, then go to a relate…

Ok so I'm developing a database to handle staff management for a Camp that works all year long. So I'm trying to make the following system. We create events training sessions in which staff member either participate or don't (I want to record each time a staff member participates) so this is a many to many relationship between Staff and training sessions mediated by a Participation Table (in which I can create a status field for confirmation, assisted, didn't show up, etc...) but I also want to record each time a staff member gets an evaluation (which is not necessarily during training sessions, but each training session does have a related evaluation). Every trai…

Excuse my newbie question. I just started using FileMaker in order to build an app for an iPad for the collection of tree data. My database is not very complicated at this point just a couple of 1 to many relationships. Project table that can have multiple Plots. Plots can have multiple Trees.  I am having trouble moving my Primary key value into my Foreign key field in my child table to maintain the relationship between my tables. I first tried to set a global variable (in a script) to my Primary key value and than set my Foreign key field calc to this variable, but this didn't work. So now I have tried using Set Field in my script to calculate the Foreign key to t…

Hi, Been working on database for years but just started on Filemaker Pro Advanced 12. Im designing something for a friend and im having trouble setting something up as I cant seem to get it clear in my head. So basically I have a couple of tables, one of which contains all of the 'customer' information (customer id, name, address etc). I have another table with some reporting entries in it, these are in two languages as I need to allow the account manager to select the English comments but the equivalent foreign language comment is saved against their 'record' (in another table with the year as a key as this will not get updated). Im having trouble set…

Hi I have three tables: Client, Orders and OrderItems. One client can have many orders and one order can have many OrderItems. Very simple relationship, right? Problem: I am trying to create a layout in which you select a Client and all the orders for the client show up in a portal setup. I am doing the following to achieve this: 1. New layout with only a body, no Header 2. Layout table is Client 3. Client field from Client table with a drop down list showing all the clients 4. Portal of Orders table. Displays data from Orders and OrderItems When I select a client from Clients drop down list, no data shows up in the Portal for that client. I know there are orders for …

I'm having trouble generating a list of transactions (using list view) that are attributed to a given Client. The aim is to have a button on the Client Details layout, that takes me to a List view (Transaction History), where I can then filter the transactions by date, and balance, for all horses owned by the client. (The transactions relate to invoices generated when their horses are treated). I have the date/balance filters sorted. The problem is that when I select the button on the Client Details page(that takes me to the List view, I am getting a list of ALL transactions for all horse owners, no the ones related to the Client I am currently viewing. I susp…

First off, I'd like to give a big thank you to all of the contributors here on the board. I decided to try my hand at creating a database recently and many of the issues I've run into have been already solved for other users by the combined knowledge of this board and others... Â ... but now I'm stuck with an issue that I'm not even sure how to phrase the search for a solution to. Â The purpose of the file I'm creating is to audit employees in our company on various policies, procedures, and guidelines in the field to ensure they are compliant. The file contains tables for (relationship diagram attached); Employees Business units (employees are assigned ot variou…

I have a table or Quotes. (Name, info, city, state, move in date, move out date, etc) I have a portal for Apartment Quotes, we send them quotes based on different properties, maybe 3 or 4 options. Each Apartment Quote has a variety of fields (cost sheet, profit, expenses, etc). If they decide to take one of the Apartment Quotes, I'd like to easily convert that to a Job. Job has customer (from customer table), property, move in, move out, size, rates, fees, etc. Is there a way to relate them, rather than make a long script and set a hundred variables?

Hi Board, Not sure where to post this. We have developed a shipping system alongside a manufacturing system that works. The bit that doesn't is the Sage Line 50 'bridge'. We're discussing removing it from the Sales Ordering Process, using FM to add orders, build shipments and issue invoices; doing a batch invoice import into Sage for the accounts. Can anyone point me in the direction of any discussion about how a db would be structured with Sales Orders, Allocating Stock to those Sales Orders, building Shipments from those Sales Orders etc.? Not sure how I'd piece it together. I'm thinking about pluggin in the Invoicing Starter Solution in FM to o…

Hi everyone, Hi have 3 tables: Employees: EmpID: number First: text Last: text Salaries: Salary: amount in dollars Date: this is either the hire date or the last increase. EmpID: number Years: Year: number EmpID: number On the Years layout I need to see for the given year how much a certain employee is making. So basically I need lookup the salary amount based on the nearest year to the current... Let's say EmpID 1, John Doe started in 2007 with a salary of $25,000 then 2009 got a raise to $31,000 finally in 2011 got another raise to $34,000 If I look at the year 2013 for EmpID I need to get to $34,000 which is the last raise before…

I'm working in filemaker pro 11 advanced. I have a problem I can't figure out. Here is the situation: I have a table called Program, a table called Staff and a Join Table called PgmStaff. I have a Layout called Program using records from the table Program. On that layout I have a portal to the PgmStaff join table where I link up a program to the staff that work at that program. So far, nothing special. What I want to do is have a button on the lines of the portal and when I select that button it launches a Pop-Up window (using records from Staff) and displaying in the record the button was on. I can create the pop-up window but I can't get it do display the rel…

Hello to all the members Well, I can not create a report with my basic recipe. In my model MyRecette, I have my recipe "Recipe 2" , I have a sub recipe "Recipe 1", and chocolate and sugar .... (not all that terrible ....). I wish to create a form for print or PDF. So one recipe can be made ​​with ingredients, recipes or both and I want to print when there is a recipe in the details . I attached my database with a model Documment as I would like so Thank you very much for your help sorry for my english, i'm french guy Recette.pdf MyRecetteHelp.fmp12.zip

Hello, a real novice question here (I know there are similar ones here, but those havent helped me), I'm trying to develop a sort of error reporting solution with 3 tables, the solution is as follows: theres one table, where the user submits an error ( lets call it table A), fills out the neccesary fields, saves and leaves, now, theres table B for mechanics, who see the the record from table A, they fill out the solution for the problem, save it, and then theres table C, wich is for purely viewing purposes only, so in short, table A has 8 fields, that are shown on table B, wich also has 8 additional fields and table C shows the total 16 fields from table B, but my probl…

I have two tables ("Schools" and "People") and I want to be able to have multiple email addresses associated with them. I would like to have one table "Email Addresses" and have a join table that can be used to find which email to pull based on the table and ID of the school/person. What is the best way to do this? I believe it is possible to do this using global fields: Schools "Schools" (global field) ID Name People "People" (global field) ID First Name Last Name Email Addresses ID Type Email JOIN Object to Email table ("People", "Schools") Object ID Email ID But I'm curious if there is a better way to do this.

In a remotely-hosted solution, I have a Payments TO which is the many side of a relationship with a Members TO. I have written a script that uses Go To Related Record from the child table (from a found set of around 2500 out of 50K+ records) to retrieve the related Members records (generating a found set of approximately 2500 records out of 11K). The found records in Payments are those which are current. The objective is to find those members whose payments are current. The first time the script is run after opening the file, the GTRR step takes over 4 minutes. Thereafter, the script is almost instantaneous. The fields at both ends of the relationship are indexed…

Hello, I am currently developing a database using fm pro 13 and i need a calculation field that writes a paragraph using related record values In my case: I have a table called event related to a table called transfusion in a one:many relationship. I have a portal on the event layout that has the field transfusion::Blood product, transfusion::Dose, etc The blood product can be RBCs, platelets, albumin and/or IVIG So each record in transfusion will have a blood product and other information. I have a layout that prints a form for the medical daycare unit. I want this form to display the information from transfusion in a coherent fashion. For exampl…

I have 3 tables: Properties, Quotes & Zips. Each property has a longitude and latitude and each Quote has a longitude and latitude. The quote lon/lats are global. Each quote has a field called City_State, which is a calculation of the City and State. This looks up the lon/lat from the matching City and State in the Zips table. I have a portal on Quotes layout that shows the properties in distance from the Quote lon/lat. It works fine, but when I go to a new record on Quotes, I have to Cut/Paste either the City or State to refresh the City_State calculation to refresh the portal. I've tried refresh window, etc and that doesn't help. Any ideas? I'm no…

Hi all,  in one table I have my database tables and fields listed.  in the other table I have some form field names extracted from fillable pdf. (thanks itext and 360works   how do I make a layout that I can show these unrelated tables, chose a row in each and have a button to click and make a record in a third table that keeps the association from one row in the pdf table to the database field I want to fill it with.  showing the unrelated tables - cant use portals.. got me baffled. making a button, not so hard  thanks in advanceÂ

Hi all, I have a 'dirty' separation model set of 2 files. In the interface file I display a global field where I can enter a number (key field) and the portal below it should update to show relevant records. This sounds so simple I can't believe it doesn't work. What's even more strange: when I look at the data file, of course, I can see the number updating as I enter the key field in the interface file and vice versa. But when I enter the number directly in the data file, the portal in the interface file updates! To be sure, we're talking about the exact same field. Change it in the data file and it works. Change it in the interface file and nothing happens. …

I am working on a Catering Space database. I have a table for the rooms. However, the issue I have is that some rooms may be divisible. A ballroom could be sold as a ballroom, or since it is divisible into 4 sections. The subrooms could be booked. I have set up an identifier that has a drop down: 1) Regular (not divisible) 2) Main (a big room that is divisible) 3) Part (1 segment of the Main) One the record for a Part I have a field to tie it to the Main (the fixed auto-assigned number). My problem is defining a calculation that is any ONE part is taken, the Main is flagged as Booked. I could have facilities that have more than one big room. I ha…

I seem to have brain lock on this problem, I keep going round and round and can’t get a handle on it. Given: A dump truck is given a ticket number for each load of gravel. Each load will be deposited in up to six numbered rail cars (usually not reused). Each rail car will contain gravel from one to three dump trucks. Problem: Track which rail cars a truck load went into and the cubic yards from the truck. Track which trucks delivered to a rail car and the cubic yards of gravel in the rail car. Help! Thanks, Al

I’ve got a FMPro12 database and there are 3 tables inside: [HitRecords], [Artists], [RE_AR]. Yep, it covers UK chart singles & albums. One disclaimer. I’ve switched from MSAccess so my understanding of some concepts may not be correct. The basic concept is this. ———————————————————— Table [HitRecords]: <ID_RE> index field, autonumber <Title> text field, title of an album/single Table [Artists]: <ID_AR> index field, autonumber <Name> text field, artist name Table [REAR] creates many-to-many relation between [HitRecords] & [Artists]: <ID_RE> number field, related record from [H…

Hi, everybody. I'm trying to create a calendar-based survey tool, where users are presented with a month-view calendar layout of the past 30 days (starting from yesterday) and click on each day to answer questions about their behavior on each day. I've (rather brutishly) accomplished the calendar piece by creating a "home" layout that has the calendar view, which is comprised of a grid of auto-calculated date fields. The table with these fields is related to a table of responses (calendar---<responses) with fields for each question asked. When a user clicks on a valid field from the calendar "home" layout, it takes them to a "responses" layout, where they answer the qu…

Howdy, all: I just can't figure out this morning why this is such a poser for me; maybe it's because instead of eating Raisin Bran I ate Raisin Brain. Please refer to the attached (which I created in .fp7 so others can see the answer though I use FMPA v13.x for everything now...including where this technique will be implemented). What I want to do is sort all Table 2's data by ascending date in Table1's portal; if a company has more than one date entered for its record in Table 2 then it will obviously need to appear more than once in the portal...which is what I want. Now, the tricky part: The way the solution this technique will be implemented in has two pse…

I have a table names Contacts. I am trying to create a relationship for a drop down list that will not include the current contact in the list. For example: I have Bob, Ted, Mary and Alice as data. When I am on Bob's record I want to have the drop down only include Ted, Mary and Alice. Thanks...

First of all, thanks for the advice I received in my last couple of posts. It has helped a great deal. I reworked the Event Management starter and have everything working and printing great. Added expenses and invoices and a bunch of other features. Here's my last little tweak. Not a necessity, but useful. All of the existing Event Management tables and relationships are intact There is a portal on the Event Details layout that contains contributors for the event. There is a button for emailing all the contributors. I would like it to only email to the contributor who has "Invoice" entered into the Role field of the contributors portal. So I need to do some sort…

I created the form that I want to include in a Fikemaker file layout. I want to be able to have someone fill it out by entering a product number and having it insert the product description based on the product number. The other fields will be manually entered. Then continue down if there is a second item, third item etc. Then I want to have a script that will email that particular record in a pdf to the home office. I'm stuck and hope someone can help. My girlfriends daughter just had quintuplets and my brain is running a bit slow, lol. Attached is the form. This website wouldn't let me send a .fp7 file Jim Product utility.pdf

I'm having an issue where when I use the GTRR script step it sometimes, but not always, returns Error 101 even though it found the related records it was supposed to. Anyone else had this issue? Since Error 101 is returning false positives, is my only way to check for no related records being found by GTRR to check if it's in the right table following the GTRR? Thanks!

Hi! I have a database with several tables, customers, sales orders, purchase orders, stock etc etc etc I also have a table that holds user preferences. I am creating a popover that is easily accessible on all layouts of my tables that gives easy access to these preferences. The preferences are linked by a relationship from each table (get account name) -> User name in the preferences table. And thats fine it works but is there an easier way to distribute this over all tables without having to manually re set the fields for the user preferences popover! Because at the moment I can copy and paste the popover to a new table but as it is a new table I have …

Hi All, I've a bit of a problem with my solution... I've been doing some major redevelopment on the file, deleting tables, instances etc etc... the solution works fine apart from when I go to delete one or more records from the main events table in my solution... Â Â I get this message - Â Â Any ideas how I can sort this problem? I'm unable to undo deleted tables... Â hope someone can help. Â Steven

I am working on a FM database and have been trying to make it do attendance electronically from each teacher. There are 26 tables total, three of which I have created to make the calendar interface that will allow teachers to input the attendance. All has gone well on that level, although I now need a way for the admin to look at a list of teachers and see if each teacher has done the attendance for each class they teach and to do this for each day that the school runs. This is what I am having trouble with. This is complicated by the fact that a teacher could mark a student absent and fulfill the duty of attendance or mark everyone as present and fulfill the task. E…

Hi, I'm trying to work up a relatively simple database to help run an organization I belong to. I've figured out that I could have tables for People, Organizations, and Memberships ( a joint between People and Organizations ). What is tripping me up is how to best account for the fact that members have to pay annual dues to renew their memberships. Also, this is a professional organization, and some members pay their own dues, while some members' dues are paid by their employer. I figure my organization would be one record in the Organizations table, and employers could be additional records in the same table. I could also add a Payments table to track checks. But I'…

In my continued quest to learn Filemaker the old fashion way (just dive in head first!), I have got stuck again. I am attempting to make a typical inventory database that consists of recipe ingredients for Dry Rubs. I have a table and have successfully created the ability to take a receipt and enter the data off the receipt to calculate "inventory in". Now the twist. I have created another table to add recipes to the database, using a portal to add related records to each recipe at a given amount for each recipe. I would like to create a button/link to push "make" recipe and have the ingredients calculated for a given size of recipe and those results be taken …

Hi all I have two tables - Table A and Table B. Table A contains all of our company's inventory. Each inventory item has a barcode number "barcodeid". Not all of the database users have permissions to add new inventory items, and sometimes certain items get overlooked. As a result, I've created a new table where our general users can request an add to the inventory system (Table B ). On the admin side, I have a table (Table B ) view that displays the requested items. One thing the user is required to enter is the barcode id that is on the item that is missing from the system (the barcode id is generated using separate software - the way this was incorporated …

Hi all Something I've been pondering for a while... Our invoices contain multiple data types: Items, Labour, Recurring services. In order to have the invoice list these data types on a single invoice I have had to have them all reside in one table, instead of the ideal which is one table for each type. The result is a lot of confusing relationships and lots of fields and hiding on the invoice layout (something that might be simpler thanks to FM13's hiding option). We have separate layouts for each data type - for the purposes of browsing. This means that there is a bit of scripting to be done i.e. to ensure that when a search is performed in the Items layo…

So I have been trying to develop a solution to program a school..... I have a file with the following fields...class, period 1, period 2, period 3 and so on. In the fields I have obvious data... class- "801", period 1- "gym" period 2- "math" so I have no problem when I find by class where they should go for each period, but I want to turn the schedule around and list it by subject as well, so if I find "Math", I would want to see period 1- *702", Period 2-"801, Period 3-"802... I assume I need to do a relationship file, but I can't for the life of me figure this out. Can anyone help?

Hi, I am a healthcare provider having interest in filemaker. I am using filemaker 12 pro. First time user. So pls excuse. I want to build a solution which maintains health reports of employees. Each employee has a distinct employee number and some characteristics like place of working, and general data like name, age sex, etc Than there will be yearly tests say test1, 2, 3,..... Multiple years like 2013, 14 I want to make a solution that can maintain and compare yearwise data. I should be able to create reports showing - employee data of particular year of all tests, comparative yearwise data of specific test, comarative data of employees. I have thoug…

I am currently creating an orders database for equipment that is sent to customers for them to build themselves. I have created 6 tables: Sites (customer site info), Orders , OrderLine, Assembly, AssemblyLine & Components. Customers order the equipment (the assemblies) & the assemblies are made up of a number of components. The equipment/assemblies can be delivered over a number of dates - hence the Phase No/Delivery date in orderline. What I want to do is to provide a report to our transport people which lists all the equipment broken down into components by delivery date/phase. I hav attached my file which will hopefully mae things a littl clear…

I am pretty new to Filemaker so I would like to apologize ahead of time.. Â I am trying to learn Filemaker the old fashion way, and just diving into it and asking questions when I get stuck. Â I am making a data base that I plan on tracking expenses for BBQ. Â In this data base, I have a table called ingredients that list of all ingredients that I use. Â It is the parent to a table I call "recipeingredients". Â (ingredientid -> ingredientidfk). Â I also have another table called "purchaseorder". Â This table is a list of transactions of buying ingredients. Â I know that prices change over time, so this table has a list layout of all purchases sorted by ingredien…

Hi all, I'm trying to create a database solution to keep track of all of our equipment at work. This includes things such as computers, computer accessories, printers, camera equipment, etc... Each item would include some details about the product such as manufacturer, model, specs. There would also be a field for the user that the product is currently assigned to. I'm fairly new to database theory and wanted to get some thoughts on how to get started. Here is a rough breakdown of the tables I think I would need: ======= product manufacturer model category (computer, camera lens, etc...) specs item product serial barcode user assigned user name …

I have a parent table that contains a global variable named gStartDate. In a child table I have placed a global variable with the identical name. It has a calculated value: ParentTable::gStartDate However, it appears that it never copies the date from ParentTable::gStartDate. How can I correct this? Thanks in advance to all for any info.

Hi, thanks for any help you can provide. Long-time FM user, but it's been a while since I did any database development. Just upgraded to FM12 Advance from FM 4.0! Set up a database back then and have been using it ever since. Â I like the Event Management Starter Solution as a base for tracking diners and retreats we host at work. But we don't have any Contributors, we only have Guests. I would like to set up a Contacts Details layout that is exactly the same as the one provided in the Event Management solution with one change. I would like the portal on the bottom of the layout to reflect the Events that the Contact has been a Guest at, rather than a Contributor to. I…

Creating a pilots logbook database. I have a layout where you enter things like the date, the type of aircraft flown, its registration etc. I want the details of the aircraft (registration and type) to be linked (or have a relationship to) another table. The reason is I want to eventually auto-populate some fields so when the person types in 'KKK' the database recognises KKK as a C172. Another reason is to keep the aircraft types uniform when searching in other layouts as you can write C172 also as C-172. How would I go about this? Cheers!

i m not sure this is the right section i ask this question.... my current database:- --------------------------------------------------------------------------- projects A ----- task table A ( task title, user, status) task table B ( task title, user, status) task table C ( task title, user, status) projects B ----- task table A ( task title, user, status) task table B ( task title, user, status) task table C ( task title, user, status) ---------------------------------------------------------------------------- I have main projects table, each project create d…

Hi, before people start saying there are tonnes of threads on this I want to explain what I want to do. I am making a database for a bunch of doctors and I have 3 tables 1. Main table KF: pk_main 2. Insurance Contact: KF: pk_Insurancecontact Child:fk_main fk_insurancecompany 3. Insurance Company KF:pk_Insurance company Main------>Insurance Contact<--------Insurance Company Now I have already implemented autofill of the address phone number etc based on pk_insurance company and it works great but, I want to implement a second criteria to autofill the this information. Each patient can have multiple insurances. If I make a new record in the insuran…

Greetings, Please don't shoot a first-timer like me... Here is the thing: I run a shop of sporting goods and I would like to create an inventory tracking system. Here are some details: - There are 4 categories: 1. Shoes, Different brands, different styles, different sizes, different colors. 2. Apparel, Different brands, different styles, different sizes, different colors. 3. Balls, Different brands, different styles, different sizes, different colors. 4. Accessories, various items, Different brands, different styles, different sizes, different colors. I would like to keep track of the invetory after each operation (entry or sale). Where do I st…

I am about to distribute a solution to many different locations - for the same client. The solution will only be accessed via iPad. I need to oversee all locations. Each location will have a large amount of data. How does everyone normally handle this process? I am thinking of each time a new location is required (this probably wouldn't happen too regularly) distributing a blank solution and hosting the new file on the server - providing them with a web clip so they can directly open the hosted file from their device with one tap. On the back end having another file that is linked to all the other files and gathers the required info. Seems like a bit more work to se…

Hi guys I have a problem with constant values. The constants I deal with are currency exchange rates, I have to set these constants once per year (maybe). Upon reading the topic http://fmforums.com/forum/topic/64043-best-way-to-store-access-constants/ I set up a table named gConstants and 1 calculation field named gConstants::EUR_to_USD, calculation result EUR_to_USD = 1,300 and I set it to global storage (options->storage->global). If I create a new record on this table (first record) the calculation field gets set to 1,3 and everything works fine: there's a script which uses the calc field value and sets another field accordingly on an unrelated t…

Hello Everyone I am working on a program for a Local Members only Night Club.. That can be open at any time of any day .. set up as follows I Have a main file that gets a entry price based on "TheDayOfWeek" TheDayOfWeek = Monday |Price= 30 TheDayOfWeek = Tuesday|Price= 10 "" , "" and is based on "OpenTime" and "Closed Time" so I used a Timestamp to relate from the main file to the Price File.. as so.. Main File .. relationship = Mainfile "SessionDate" ≥ TimestampOpen and relationship = Mainfile "SessionDate" ≤ TimestampClose result = Price for Date Today or For Date one day before if "TimeClosed" is after Midnight the next Day The Problem I am getting is wh…

This is the relationship I have. It really works well I can have unlimited Topics for a piece of Information. Meaning Subtopic for Topics and so on. I have created a couple scripts that can deduce what I am calling the family. What I mean by family is all the members under the Information, every Topic and its Subtopics no matter how deep. So now with this list, could it become something I could receive benefit from storing in another table? My intuition says definitely, but I just want to be sure this is the way to go. I really would like to have a way to keep users in â��subject-matterâ�� only the Information and its Topics and Subtopic. Is there a way to keep users lock…

Hi everyone My first ever post. Getting to grips with filemaker, absolutely love it but im such a novice, taking me ages to create a decent database. Im an optometrist making a database to hold my patients' information, i.e. Personal data and their spectacle prescriptions. Now, i have a table and layout for 'Patients' which has many tabs with info from related tables. I have set up relationships correctly and can successfully implement 'portals' to display different related data in different tabs. One of the tabs is spectacle prescriptions. Now, its easy for me to create a portal in the patients layout on one of the tabs and then have the data from the Prescri…

I am learning a multi-user design. I have 2 tables, Beasts and Items, that are in a many-to-many relationship using a join table. Each Beast and each Item has a flag that denotes whether the user has killed the beast or collected the item. All the flags are Yes and No. The interface should be the user sees a layout of all Beasts with his user flags and a layout of all Items with his user flags. Very simple ERD for a single user. What's a good way to do this for multiple users?

Hello, I am trying to make an Invoice template with different Auto number for Proforma, and different for original like Ivoice ID when I press new record but, when I select proforma, I would like to increase a number field by 1 and when I select original then a different number field to increase by 1. I am working on Invoice template from starter solution. My filemaker is Version 12. I don't know how can I do that. Is it possible? Could you help me? Thank you in advance!! Nikos

HAving difficulty getting this to work: have table of records each with a from and to date. have global from and to dates. have a clac field in each table record (isConcurent?) set to yes/no if the from and to dates of the table record fall anywhere within the global date range. have a relationship in table based on global field containing "yes" as equal to "isConcurrent?" I want to on a layout for any table record, a portal showing all other table records within the global date range. Portal never shows any records.

Hiâ�¦  I'm relatively new to FM so please forgive the level of this question.  I have created four tables as follows:  Customer JobSite Equipment Service  Each Customer can have many JobSites, each JobSite can have many pieces of Equipment, and each JobSite can have many Services.  I've tried to create a button that creates a new JobSite record from the Customer layout. I created a script and attached the script to the button on the Customers layout. However, I'm clearly "not getting it".  It would be much appreciated if someone could take a look to see what I've done wrong. Seems I can't upload a .fmp12 file so here's the script:  Set Va…

Hi - I'm new to FMforums, but have been reading in here for some time. My challenge is a database, where I need to display some points earned by members in different categories and display their individual points so far - and finally have a tab/layout/button, where new records of points can be entered Plz find file attached. Any help will be appreciated Martin TølbøllClubPoint-vers.-2013.11.05.01.zip

Hi All I'm working on a real estate investment sales DB I have a file with three main tables; Deals, Investors and Opportunities. An opportunity is a join of deals and investors. Whenever an investor is interested in a deal we create an opportunity. Because it’s real estate, when one investor closes on a deal all the other opportunities are no longer valid. We would like to keep them in whatever state they finished up because it's a good historical record for investor activity. On the “Home” screen I want to have a portal showing all open opportunities - where the deal is not expired or sold. I have a calculated field in the opportunities table that…

Hi, I am making a CRM database for my company and I'm having trouble understanding the fundamentals of using table occurrences and multiple criteria relationships to create specific layouts. I have a Contacts table in a many to many relationship with a Tags table. They are joined by a Tracking table that has a "Reminder" check box, a "Primary Partner" field (i.e. "Staff") , and a Next Contact date. From here, I want to create layouts for each Primary Partner that only list all the records with the Reminder check box filled and sorted by Next Contact date. (Ultimately, I'm aiming to script automatic emails with these lists to be sent out at regular intervals). …

:·) Hoping for some experience, advice/tips on this topic...  How to properly set up an InvoiceTable with child of ItemTable. ItemTable would track both charges to client and payments from client. Then Payments would be attached to the BankTable to track "real" money. Note; BankTable would also track expenses.  1) Should charges and payments share the same amount_field � or should there be a charge_field and a payment_field?  2) How to attached/create a bank record to a payment  I've attached screen shots and demo file to help illustrate my questions.    Super thanks to any insight on this one!!!  invoice_item_pmt_demo.fmp12.zip

The main table of my database is Projects, this is related as a child record to Companies which in turn has Representatives_of_Companies as a child table. Within the Project table there are fields that are to be populated via the Companies table, namely Client and Associate_Company (ten fields from 1 to 10). At the moment the Project table has an fk_client_serial for the Client field but the Associate_Company (1-10) only use a list derived from Companies with explicit relation and pk/fk interchange. So, my query, is simple. Is this good practise the way I am going about this or is it just an inept way to go about it. Should I try to create a new TO for…

Hi Im new to FM and database thinking, so please be gentle with me . Im still evaluating FM 12 and haven't yet decided if it is the right way to go. But from what i have seen so far, it has everything we need + a lot more, so it is only this "application" left to sort out. My goal with the database is to create a simple quality system for a manufacturing process. I want to log the type of materials (and their batch numbers) used for certain products. I have searched and searched, but i still haven't figured out how to do it. Probably because it is so easy, i cant see it. ill try to explain the process. And give the desired outcome of the data …

I have two tables, called "retailers" and "discounts". The "retailers" table includes fields called "id" and "name" The "discounts" table includes fields called "retailer_id" and "retailer_name". I want the content of 'retailers:name' to be inserted into the field 'discounts:retailer_name' in every record where the fields 'discounts:retailer_id' and 'retailers:id' are matching. I am sure that in old versions of FMPro there used to be a simple "lookup" function that would be used to achieve this, but it seems to me that a relationship needs to be set up before this can be done in…

I cannot find it but I read to create 500 fields in every table. Then name and use them but leave the ones you do not use. I saw someone's file also where this was done except there were only 200 fields like Field25, Field26, but they said they did not do it and they did not know why. I search for 'extra fields' and get nothing similar. Why do people sometimes do this? I realize I posted in Relationships and this isn't relationships but I did not know where else to put it. Thank you.

Ok So I have multiple tables set up to filter out data and show specifically the data for each different office that we have across the state everything works perfect in the following relationship Table 1 Table 2 Administration Board ----------------------- Service Locations Office location = Office Location This works perfect so the Administrators at different offices only see the service locations related to their Office and not all the rest Here comes the question , I am working on an oversight board that the Direct…

Since Bento iis no longer updated, I started my db for my students over with FM Go. Please consider three tables :students:, :subjects:, :aplications: During time, students have several aplications on various subjects, ok? In each application, students updates their address, email and... photo In students main layout I have successfully created a portal with sorted aplications over time. I also have a beautifil big portrait near the name and wish that portrait to display the photo of the most recent aplication available for that student. So :students::portrait is a container with calculated value GetNthRecord (:aplications::aplications_photo;0) I unchec…

I am writing a book about privacy and identity, as part of the research phase I am finding that I need several databases. I done OK, so far, but my attempts at creating relational database today has left me stumped. I am ashamed to admit that I have worked in Filemaker for sometime but it is not my regular gig and after returning, post long hiatus, I find I am not so familiar with my old friend. I have to search online for for various terms related to the subject, often in other languages. I have to keep pairs of search terms: English and other language. Paper lists are not cutting it anymore. Its quite possible my concept is flawed, if so, I am more than willing …

Hi all, I'm unsure this is the right place to ask this question but here goes... In my solution I have an Events table which is link via a join table to a contacts table as it's a man to many relationship, so I have the Contact Fk and the Events FK in the join table to make the relatonship. Previously I had restricted the contact info to just two contacts per event, so those contact fields (name, Mobile No) were in the events table, this made it easy to concatenate the mobile numbers to place in a 'Send sms' URL string... which worked perfectly... Now I have the contact data in a seperate related table, with individual records for each contact, and I am stumpe…

Hi all, I have multiple table instances in my solution, for various reasons... some are search or filtering to a portal in a different table, based on a global search field, others are connected to other tables for pulling data into those tables... My Question is... having all these seperate instances of the same table seems a messy and convoluted way forward... is there a way around this? And before anyone mentions it, I know I can create portal filters for searching and filtering related tables. Am I just missing something critical?? Steve

Renting & managing condos for people. An person can be an Owner or a Renter or both even at same time. And they can rent another Owners rental. One Owner can own many Rentals. And one Rental can have many Owners (partners in purchase). Multiple Owners also have co-rented a third owner's condo. I have imported all the address books into People table and excel spreadsheets and currently have following additional tables: Condos holds details on the condo and mainContact from People (required for Responsibility Clause). Owners holds ownership rights, financial aspects associated with other partners owning same Condo, based upon their ownership percentages. …

I'm creating a simple job application tracker, one for practice, secondly for practical application for some students. I created two tables, Company and Position. Primary key is CO ID in Company with the foreign key in Position. I included screenshots. I simply want to be able to when in the data entry for Position to be able to use a drop down to select a company, then have the CO ID populate. I for the life of me cannot get the drop down to work properly or populate the CO ID. I'm new to FM, so be gentle please, but understand relational DB's. One thing I'm also not sure of, when doing the layout, should the Company Field in the Position Layout point to the…

Say you have an invoice for widgets. One customer pays X, another pays Y for the same widget. How do I construct a relationship between INVOICES and PRODUCT PRICES that would allow this? And further, what if the price I charge customers increases. How do I keep an old invoice from showing the newer values? And when I make a new invoice, how would I populate a value list for the widget so I would see all the prices paid by all previous customers? I looked at the starter DBs for some clues but don't see how they handled this, which must be a pretty common feature of things like proposals and invoices, no? Maybe I need to think about this in a different way?

Hi, I am very new to creating relationships in filemaker and am having trouble accomplishing a specific task. Currently what I have are 5 different tables. I have the tables applicant_profile, applicant_reviews, evalutaions, questions, answers. What I am trying to accomplish is the following: Each applicant in the applicant_profile table will be reviewed multiple times by a review panel that will store general comments and reviewer's userID in the applicant_reviews table. The evaluations table is the collection of the questions from the questions table that will be displayed to the "reviewer" and the answers table should be the answers to each question. The p…

I'm having trouble determining the best setup for this database. It has, at the core, figures and products. a figure being a particular toy, a product being that figure in its box or multiple figures in a box. So I've got a figures tables and a product table and a "product contents" or product/figure association table. That all works fine. Where I get stumped is when there's different "levels" of products. For instance, one of the products we're trying to track is a box that contains up to 24 smaller boxes with figures in them. This Super Product contains multiple other products, but is basically just a box. like actual physical 4-sides-and-a-bottom made of cardboard…

Hi, I have a relationship that is troublesome. Basically, the table field I need is not able to index via a calculation. Hmm :-( In the client table, a value is entered depending on the client status. ( TEXT ) Over in my remedies table, I need to see this field. Which I can build a relationship via my remedies table to my client table. However, this field can not be index and used to build another relationship on my remedies table. Basically, I was hoping to use the data in the client table to determine a "=" in a relationship on my remedies table. I can hard code the client status when creating the remedy record into a field on the remedies t…

I have a table. and I have done related list. The related list shows all records .When I try to find somthing in the fields. for examble, it finds 2 records in totall 5 records. But in the related list, still shows all records.I want to see the found records in the related list.if İt is possible or not. Great Thanks for your helps. Example.zip

I am new to Filemaker and have set up a simple database with two tables: "Patient" and "Journal" They are created in a one to many relationsship with the field "PatientId" in the patient table and "PatientIdFk" in the journal table. Â What I would like to create is a script so I can run that script via a button on a layout called "patient" and create a new journal record (and by the same time go to the layout I have called "journal") Each patient can have more journals. Â I know I can do that through a portal, but it is unfortunately not the best way for me, and I would like to do that via a click on a button outside the portal. Â Hope it all make sense. Â I…

Hello, I`ve got a table with pdfs(containers), some of these are related to another table but som arent, should I have all pdf.s in the same table or should I separate them so I have related pdf.s in 1 table and unrelated in another table? Really appreciate your help.

Hello Here again I ask for your help to build this database. I've traveled the forum but I did not find my happiness .... : whistle: Here I want to create a database to manage recipes and be able to calculate my production costs. I tried several "formulas" but my big problem is the sub-recipes. In fact, a recipe is a set of ingredients and the same recipe can also become an ingredient. I tried to copy the recipe with all necessary information (name, price per kilo, ...) in my ingredient table, what works. But in case of modification (the recipe itself or tariffs ingredients) of this recipe, the changes are not taken into account which is very annoying and if you have…

Here goes my first FM Forums post. Please bear with me. I am working for an organization that is probably not very unique in its needs, but I am a bit of a meticulous perfectionist, and I can't find the absolute best way to scheme this relationship. Our organization has families, individuals and students who participate in programs. Some programs are free and others cost money. Our programs are occasionally co-sponsored by outside organizations. Additionally, we're a non-profit that accepts donations and grants from organizations, parishes, and individual/family donors. We want to track information about our program participants. Specifically, we want to …

Hello. I have two databases; one showing clients info (name, DOB, address, phone and email) and the other with info about the books borrowed by these clients and the dates they were borrowed and returned. I want to have a portal at the bottom of the layout for each of my clients (Clients database) showing the books that each client has borrowed in the past, like a borrowing history for the Client. In addition to their personal info, this borrowing history should appear automatically in the portal in the Clients database every time I create a new record in the Books database. I don't know how to make it happen. Can somebody help? Thanks! NR

I have a brand new database that I will be sharing on the network using FM 12 Server Advanced and Instant Web Publishing. This new database will bring in data from 4 different databases to be viewed in one record of this new database. Some of this data brings in 4 different container fields. Three of them are brought into a field in this new database via a calculation (If (isValid…). The last is a showing data via a relationship that I set up. All currently show up fine via IWP. I would like to make them all referenced fields to keep speeds up and also so that this new database is updated in the background while data is modified in the other databases. If more info is n…

Hi, I've have a database with 3 tables a// contract (all contract per clients) b// client base (all personal info of the client) c// company base (the company information) in the client base I created a calculation field: Company:CompanyName & Name this last field i need to use as a value list in the contract base but it won't allow me because i can't index it how can i find a solution for this problem? I've tried a lot, but i can't solve it Is someone able to help me? Thank you very much Filip Langenbick

I have a relation that doesnt seem to work right. I can't see to get my head around what is wrong. It for a school - tracking absences. Each class is once a week and has a weekday (courseDay). Each class has the tipical many-to-many of courses and student enrolled in it (Course_Students). I have made a dashboard that takes the a day of the week to show the students that should be in the school for all classes on that date. The relation is: Dashboard::date_of_week -> Courses::courseDay -> Course_Students::courseID -> Students::studentID When listed in a portal, all the course names are the same and there isnt the correct students. I have tr…

Is it possible to show multiple instances of same (non-duplicate) record in a single portal? I assume this isn't possible but I thought maybe I could use a join table or something?

I am trying to chart and analyze sale data. In particular, "Most popular product(from a specific product category) this quarter/month/year/etc" and things of that nature. In this database there are several tables: I will try to explain how they are linked Sales Order -Sales Order Line Item --Product Product Category -Product --Sales Order Line Item For example, I want to see the most popular product during a certain fiscal quarter do do so, I need to filter the line items by the following criteria and then have a calculation of the sum of found line items grouped by product * order quantity: Based on the tables this criteria needs to be met: Sa…

I am trying to use a FM database to record the responses of study participants to a series of surveys. They will complete the studies on an iPad using FM Go. I have created a table for Subjects and separate tables for each survey. I want to create a relationship between the subject and each survey, and I've set up these relationships using the SubjectID as the primary key in the Subject table and foreign keys in each survey table. Participants will first complete the subject table then go to another layout for the first survey. My problem is that I cannot figure out how to automatically relate the SubjectID to the new survey layout. I can do it using a pop-up menu,…

Hello, Â Somewhat of a novice and at the very early stages of planning an equipment rental solution to track equipment rentals, perform invoicing and some CRM. Before I delve further into adding fields/layouts etc., I'd like confirmation that my relationship graph is sound. My thoughts are that the join table "rentals" would be where all invoicing would be performed. Is this assumption correct? Â File attached shows the relationship graph. Any feedback would be appreciated. My apologies if this post is in the incorrect location. Â Ian