Relationships

Two files- Clients and Notes. A Client can have many Notes. I want the clients name in Client>ListView to be red when his most recent note is more than 30 days old. I've gotten a portal list of dates to turn color appropriately but can't figure out how the field in Clients can 'know' which record in Notes to cue the conditional formatting. I have a field in Notes that returns a '1' if stale and a '0' if not. [email protected]

I know that this should be easy but I just cannot get my head around it. The file attached is for viewing records (for advertisements) which are imported into Filemaker from an external data source. I have set up a ViewerTable to allow the user to see the associated records which are related by: ViewerTable:SalesNumber — OrderTable:SalesNumber OrderTable:OrderNumber - OrderInsertionTable:OrderNumber The relationship between OrderTable and OrderInsetionTable is One To Many My question is how do I view records from the OrderInsertionTable from the point of view of the ViewerTable using the criteria: SalesNumber = SalesNumber StartDate ≥ RunDate …

I currently have two tables. Account Executive table and a related File table. I'm trying to figure out how to reassign Files to a different account executive.... When i try to change a filed reflecting the account executive's name (ie. change from Adam to John) it directly changes the related record in the parent table and I end up with duplicates (parent records Adam and John turn into John and John). Does anyone have any suggestions how I can do this?

In an event management solution... There are many events, each attended by many teachers. And each teacher can attend many events. So far, so good -- I created a join table called 'registration' between 'events' and 'teachers.' Now I need to add a layer of complexity and track activities. To keep it simple, let's track meals. Each event includes several meals. Each meal will be attended by many teachers (obviously the teachers in the 'registration' table for this particular event). I don't know where to connect up the meals so I can show the teachers eating each meal or conversly, the meals each teacher attended. If the relationships are: even…

I have two tables "CANDIDATES" and "JOBS" Each shares 3 fields, "Title", Department" and "Area". I want to create a List or Table view layout that shows ONLY the matches, ie combinations of one record in each table where all 3 fields match. Is this done with a "join table" and how? I tried just creating a separate "Match" table with the fields realated but I don't know how to populate it.

Hi... I have one control layout based on some global table. I have one master & a child table. I now need to show all the main records along with their respective child records in one single portal on control layout. Please suggest. pushkraj

Hi To All, Hoping someone has a smarter way of doing a multiple date range summary report. I have two fields which specify a date range. I then have fields which divide this range into days months or weeks depending on the user selection. I then use multiple relationships to grab and summarise data between the multiple date ranges. Ie 12 months = 12 ranges. It just all starts to get very laborious when trying to do 52 weeks!! I don't want to use scripts all should be calcs. As I deploy this on the web. Any ideas much appreciated. Thanks, Lance

I have been reading quite a bit and came across some articles by Len Silverston. Of particular interest to me was the Supertype and Subtyping of entities. It makes a lot of sense to me. The "Party" concept touches on the fact that People and Organizations are in fact one superentity: party. And that a person can be subtyped into other entities; same for organizations. I am sure I have mauled the definition, so clarify as much as you wish. My real goal is to actually implement this in FileMaker. Has anyone ever modeled this in FileMaker? Opinions on the Pros and Cons from those who have implemented this would be very helpful. The "concept" is what I have a …

I have a "table" of data (excel spreadsheet). I am trying to build tables that will normalize the data from the spreadsheet. The spreadsheet consists of these rows: continents countries states counties cities zip codes latitude longitude I am building separate tables in FileMaker as such: continents countries states counties cities zipcodes (will hold lat and longitude as fields) I have been able to re-establish the relationships by somewhat of a hack method up until I got to the "cities" table. In other words, I established: countries to their continents; states to their countries; counties to their states; but I …

My situatinon is this... Relation (one to many). I have a portal showing related data and a summary field (that belongs to the related table) showing a summ of all that related records... But then i want a summary of all that summarys of the child table on my main table also but it dont works..and filemaker dont let me summarize a relateds sumary.

I hope this makes sense ... I am trying to create an Excel report of client's kids and their medical conditions. Working with 6 tables/record sets: Parent Child (can be multiples per parent) 4 individual medical tables (multiples possible in each per child) How can I 'combine' these seperate records into a single 'record' so everything dumps out to a single Excel row? I saw another entry out here that seemed to use Calc fields for this . Is this what I should look at for this need? Right now, I am creating 4 seperate reports (using each medical table as the context). It's pretty ugly.

Consider Table A and Table B. I have more than one related pairings of these tables like this: A to B on serial, delete related records in B when records in A are deleted. A1 to B1 on serial, delete related records not checked here. There is no connection between AtoB and A1toB1. Now I'm in a layout based on A1 to B1 and I delete an A record. All related be records are being deleted as though I was in a layout based on the first relationship. Am I misunderstanding this feature? Regards, Don Drake

I'm working with Filemaker Pro 8 on a PC. I have one file called Adults.fp7. Each adult has a unique ID as well name and address fields (fullname, address1, address2, citystatezip, and phone) I have another file called Students.fp7. In this file, I have, among other fields, four fields called Parent1, Parent2, Parent3, Parent4 (in today's world, this family arrangement is not uncommon). In those fields, I enter the unique ID from the Adults database. I want to then display from the Adults database the relevant name, address, , and phone information for each of the four parents. I've tried setting up the relationships in both databases in various way…

While I'm not a noob, my skills are somewhat limited. I can usually find a way to make fmp do what I want, but sometimes it's an ugly hack job. In this case, what I have is "functional", but I know there's got to be a MUCH easier way to do this. Here's my scenario: The purpose of this db solution is to keep track of all the miles traveled per state, and fuel purchased per state. This info is tracked per trip, per truck, per customer. Each Customer can have several trucks. Each truck can have several trips. I use the Calc Sheet DB to provide a snapshot overview of total miles per state and total fuel per state, along with grand totals per a set of trips for …

I need to find the closest address for an airport. I have an airport database (Apt) which has the airport manager's address, but half of the time it's not at the airport. I've geocoded the address and know how far it is from the center of the airport. I also have FBO addresses (businesses on the field) in a separate table (FBO) and have geocoded them as well. They relate to the Apt records via an ID field. What I need to do is find the nearest address to the center of each airport between the one Apt address and the one or more FBO addresses.

I am a beginner to filemaker and have a question regarding a database I am trying to make. Basically I have 2 forms. The first form is a list of ALL the furniture [color:red]("furniture catalog") including Name, Description, Material, Price, Picture. The second form is a list of furniture by job [color:red]("job furniture") including the same information from the [color:red]"furniture catalog" including a few other fields like location, quantity, remarks, etc. After filling in all the information in the [color:red]"furniture catalog" I want to be able to go to the [color:red]"job furniture" form and select a piece of furniture that is in the [color:red]"furnitur…

Hi, Is there a maximum length for a text primary key? The table create a primary which merge a serial number field with the computer MAC address. The relationship between the child table and the parent works on a PC with a 48-bit address but not on the UMPC which seems to have a longer address. On the UMPC, a problem occur when I created a child data from the parent. Whenever I create a record from a portal in the parent layout all the records from the child table where showing. This problem was not occuring on the PC. Is there any reason? BTW, on the UMPC it's an offline version of the server. It's the reason why I use the MAC address as the …

Hello, I am completely new to FileMaker and I am trying to create a purchase order database. I need help with creation of multiple new records in a related table through a portal (or other way). I have three tables: "orders", "suppliers" and "products". These are connected by the following relationships: "orders:SupplierName = suppliers:SupplierName" and "orders:productID = products:productID". I would like to be able to create new records in the "suppliers" and "products" tables through filling up the purchase order layout. I created a portal for supplier displaying related fields (city, street etc). If I enter into the orders:supplierName field a new …

Hi there I am a bit puzzled with my records. I store my members' photos in a separate folder. When I try to delete a record, I got the following message: This record cannot be modified in this window because it is already being modified in a different window. I use the following actions to store my members' photos: Set Error Capture (on) Go to Field (Member::Member Photo) Insert Picture (Reference) Go to Layout (Member Record) Any help is welcome! ;)

I'm an old hand with FMP6, have done a fair amount of SQL, and have now been asked to develop in FMP9 Advanced. It's early days, but it's going well. However the fm7 file format has some puzzling ways of handling relationships. I've looked without success for guidance on FMI's site for something that explains concepts such as base tables, anchors and the like. I've also looked without success so far on FileMakerMagazine.com's site. Would appreciate some direction to docs that would fill the gap. Appreciated, Morley Chalmers

Hi all, I'm kinda new to this but been around programming and filemaker for a while now. I would like to be able to create 18 records with similar core information in one table from a related table and I'm not sure how to do this. Basically I am registering a learner on a programme and there are 18 separate units on the course. I want one record per unit in my Modules table to reflect this, which is linked to the learner information table. When i have added a new learner to this table I want to be able to hit a button and have the 18 records - 1 for each unit, created in the Modules table and ready for recording their progress through the course. Doe…

Hi guys, This question is based on the purchase cycle of a firm and how to manage the reconciliation of Purchase Orders (POs) that we generate with the corresponing deliveries and Invoices of our suppliers. I have chosen not to manage delivery notes in the database and am only concerned with tracking invoices since an invoice has all the info of a delivery note and much more. Basically we've been running our bar without POs. An employee simply does a cursory inventory and calls the suppliers to request more liquor. I would like to change this process so that we used printed POs which can then be used to check the delivery when it arrives. The purchases are made …

Two Newbie Questions: 1. How do you know when to make something a value list as opposed to a series of fields? 2. When creating a join table, do you use the fields from the existing tables in the join table or create new fields that have relationships with the existing fields?

Hi Guys, I wonder if someone could point me in the right direction as i'm finding it difficult to structure this part of the DB. The solution i'm working on is part of an inventory system for a bar. Every menu item has it's own record in a MENU table with respecive price, category, etc. and this is linked to a RECIPES table where the contents of each drink is listed (eg. a martini may include a number of different liquors). The RECIPES table holds the foriegn keys of a PRODUCTS table and obviously a field for the quantity of product used. Two other tables, SALES and SALES LINES, register each and every sale made, using a foreign key that references the MENU…

I'm semi new at this, been working on a solution for flying club aircraft rental payment tracking. What I've come up with in theory is tracking individual flights like a check register. My reporting period is based on the calendar month, and I usually have a starting and stopping meter reading that everything falls into for between that month. Though I guess that could also be the lowest and highest value of a particular months records. So what I need to see if I can grasp is, it sounds like a check register would be a database within a database. Or would it be a report/layout that would do this? My data consists of Flight Date Who rented it, the met…

Hi Guys, What i would like to do is provide a filtered set of data via a group of two table occurrences based on the same base table (ie. a self join) Let's say i have a products table that contains absolutely all the products that have ever passed through my company and in that table i have a boolean field which marks the products that are currently active. There is also a global which contains the number '1'. I then join the two TOs via the global field and the active products field. This way i hope to filter the resulting products list to show only those products which are currently active. Now comes the big question... I know i can achieve this lis…

I have 3 current table-- Store, Store Reviews, Store Questions The relationship between Store and Store Review is one to many--Each Store can have Many Reviews. : But I can not figure out-Store Reviews and Store Questions and need some help. [color:green]1st--Each Store Review can have many Store Questions BUT Each Store Question can be used in many Store Reviews(I think) : [color:orange]Data Example--Store question might be--- [color:green]What was your overall experience in the Store? That can be used for multiple store reviews, right? But a specific question might only be related to a few stores [color:green]What was your rating of f…

Before burdening the list, I did some forum searching, but I did not come up with something directly on point. I have been collecting the data for a school directory in the following tables: 1) family_info table (includes the last name, parents, address, contact numbers, emails, etc., plus a Family_ID serial number key) 2) student info table (includes the first name, grade, teacher, plus a Student_ID serial number key) There is a one to many relationship between families and students (one family may have several students in the school). I have created a relationship between the student table and the family table based on the family ID. Now I w…

HI. New to FM. I'm understanding many of the basics, but not getting some. Here's my dilemma: I have a table of physical fitness test scores within 6 tests: (mile run, etc...). Each test has a preferred range of scores (a hi-value and a lo-value). These value's change depending on two variables: gender and age of the student. So I wrote a table (I call "Standards") that recorded the age & gender and the corresponding values for them in the various fitness tests. Now, I create another table ("Students") I create age/gender fields that the user selects from a drop-down list and fields with same names as the hi/lo scores. I made these fields (the fitness score…

Hi I am trying to autofill part of a record using Lookups, but I don't know how to get it to find the most recent related record. In more detail I have a call record, which includes a patients medical history within it. If that patient calls again I want to create a new record which I've done and pull through the most recent medical history so I don't need to re-enter. I can get it to pull through the relevant data using lookups, but not the most recent - please help. Thanks Mark

Hi folks - I get the feeling this is staring me in the face, but I just can't seem to get it. I've been spending hours reading, but I can't get that missing conceptual link. I'm an optometrist. I have many patients. Each patient has many consultations. When I see a patient, I want to start in that patient's details screen, update them, and then click a button that will open a new consultation screen, which will be automatically associated with that patient, showing name and age, etc. (I guess it's no different to having a group of Customers, all of whom have many Orders). But how do I get the new consult to 'know' it is associated with that patient? I'…

I'm having a slight problem with the sub-summary display. Currently I am trying to display information from several product series, and as far as I can tell, I have setup the subsummary correctly. However all the information that gets displayed is the first record from each series. I.E. I have 50 different records associated with product series A, only record 1 is present. I should add that currently product series is the only field in the sub-summary part and the rest of my fields are in the body is there some setup issues that I have overlooked? Or is it completely different. Any help would be greatly appreciated

I did some testing, and apparently I can't relate records in two tables if I set two fields equal in the relationship and they are both empty. Which is what I want. I guess I need to set up a calculation that only enters "None" in both tables if there isn't already data in the field? Is there another option? -Jeff

So I've been racking my brain on this one. The file is made to track families based around a Mother and Father (each with ids) and marriages. I can show all family using a family id, sibling based on marriage id, mother and fathers children based on their ids but in different portals. I'm asking for help with trying show children for either the mother or the father in one portal. I've been reading some about Multi-key (relatedtable::ID & ¶ & ID) relationships but i'm not sure if how i have the database setup breaks that or most likely its just outside my skills set for now. It would be greatly appreciated if anyone would take a look at this…

I am working on a membership list of Band members. All the members are entered into the table Clients. Is there a way to enter a FATHER and a MOTHER with some of there own data on the same Input layer as the client. I have created a copy of the table. Use a unique Band member number in both tables. When I try the portal method, I can get the first field to be that of the father or mother, however related fields in the portal take on the client's detail instead of the mother's or father's. I am willing to buy a good book if someone can recommend one.

I want to make a link between a calculated field in one table to a regular field in another. Can anyone help? As a M$ SQL guy, I’m used to writing raw SQL which can join unrelated tables to get at what I need. It doesn’t appear that’s available for Filemaker though, but there must be some approach that’ll work. Here’s the business logic behind this: An employee can work for months or years, leave and come back. Employee seniority is determined by adding all hired/terminate date time spans together. Depending upon number of years served, each employee receives an award for time worked (5 year award, 10 year award, etc.) I would like to see for a given employ…

i have a fully working database in filemaker dev 6. not i need to add something but cant find how to make it work. for each customer i want to track their spending and get a total for each customer. i want this to show inside the record for the individual customer but when i add payments it creates a new record. PLEASE HELP!!! luke from uk but in malaysia

I am having problems in understanding it ive tried many guides but i still dont understand it. Maybe if someone explain me with their own words.

Howdy, all: In nearly all of the FM tomes and tutorial DVDs I have, the authors recommend reducing table relationships in a solution down to only one-to-many (or many-to-one if you're a pessimist) relationships. I can "grok" that, but I'm having a devil of a time trying to reduce a new solution from what seems like only many-to-many relationships. Background: I write engineering orders ("EOs") for the aircraft in our airline's fleet; they instruct the mechanics how to modify or repair an aircraft in accordance with the issuance of AD (Airworthy Directive) notices, service bulletins, and other documentation. Because the "owners manuals" of the aircraft are revis…

Really I did not know where to put this topic, I hope it is in the right place. So the question, what happened to my record base, when I press delete this record, FM deletes all the records. Might is sible but I do not know it. I create this file in FM 8.5 now I have FM 9.0 advanced.

Hey all, I reviewed Matt Petrosky's file sometime ago using relationships that created records within the same table that allowed him to create a hierarchy that can then be expanded with some scritpting to give the UI a sense of expanding folders. I also looked at Seedcodes unlocked version with similar structure. Unfortunately my solution requires separation of the entities due to drastic differences in there attributes. Since the two examples both use creating records in a single table...is it possible to create a similar structure using separate tables? Thanks Mike

I have a Project table with many movies and a actors table. I want to relat actors with movies with buttons. So what i want is that the user being able to relat actors with movies freely.

Please help this newbie! I wrote a FM 5.5 database for my business in 2001 and now I am rewriting it in FM 9. We organize group tours and the DB has a table for Groups, which is related to a table for Clients (i.e., individual members of each group) by a "Group Name" field. On the Client table, there is a "status" field to note each client's status (Active, Canceled, etc). On the Group table, I want to summarize for each Group how many clients from that group are Active, Canceled, etc. I have been working on this for a week now and have made no progress! I see from other posts on this forum how to summarize this kind of thing for all records, but my need is to summar…

I have two databases. 1 for software and 1 for computers. A computer can have many different softwares on it and the software can be installed on several machines. Is there a way to access all of each from each other? Example, I add a new computer and then add the softwares installed on it. Then I go to the software database and select the software and see that is was installed on that new computer. I am not really asking how to do it so much as find out if it is possible. Thanks. -Derryl

So, my problem is following. I made program that is running from start of 2005 without problem. Few months ago something happened and records started to disappear... Main table is ok ... then related record moves to another record of main record ... and they are joined together with one to many relationship over primary key ... so ... main table records stay stable, related redords disappear or just "move????"... IS there any way I can find those missing records? Or to reconnect them? I tried compact .. but nothing happened ... PLEASE HELP MEEEE!!

Hi Everyone, I've got two tables, "People" and "Campaigns" which are joined by a join table, "Camp_Join", to make a many to many relationship, via People_ID and Campaign_ID fields. The "Campaign" table gets the person's email address from the "People" file, and i want to check that there aren't duplicate email addresses. I've already got a self join of the "Camp_Join" table working to check whether the same person is in the "Campaign" file twice, but when i try to do the same for the Email Field in "Camp_Join" table, i can't because that self join key is a calculation, ie Campaign_ID & "_" & People_Campaign::Email, and as you know you can't base a relationsh…

Hello all, I'm in a bit of a quandary and would like to ask the experts here for their opinions. I'm cataloging the placement of art in many rooms of many buildings. The "room" table has ID fields for each of the 4 walls which comprise it. The "wall" table has an ID field for the room. We can't have orphaned walls (walls that aren't assigned to rooms), or rooms without 4 walls, so those ID fields are required. There's my circle. It seems like I'm going to have to remove the "not null" requirement for the wall ids in "rooms" because the logical progression of data entry dictates that rooms are added before walls. Removing that not null requiremen…

Hello all, Please forgive me as its been awhile since I've worked in FMP and I'm sure I'm just a little rusty. So here is the problem I three tables lets call them A,B,C table A has a field called ID containing a string table B has three fields ID which is related to table A's ID and a field COUNT which is a calculation field that contains count(A:ID) resulting in a the number of records in A that contain the ID. MyBOOLEAN_A which is a calculation that checks to see if count is greater than 10(as an example) table C contains 1 field MyBOOLEAN_B set to 1 or true and related to table B and I have a portal show…

Good Morning, When I enter an item # to do a look up for name, size and price of an item that is entered into a portal, if the item does not have a price because it varies from invoice to invoice, the manually entered amount on the invoice getis entered back to the look up file, so the next time that item # is entered, it brings up the previously entered amount. I want it to remain empty. What have I missed in setting this up so it won't do that? thanks LJ

I'm having trouble figuring out how to do this: I have two tables, with two fields in each. in table1, i have fieldA, which always has one single name (i.e "John") and FieldB, which will have a list of things (i.e. "A¶B¶C") in table2, i have a record for all the possible things that might be in any list in fieldB (say 26 records, where FieldC = "A" in record1, "B" in record2, ...) Then there's FieldD, in table2, which I want to equal a list of all the names (values in FieldA, Table1) which list the value in fieldC (table2) in FieldB (table1) I'm having trouble figuring this out.

Let [Tables] and "Fields" I have [Contacts] linked to [Companies] by "Company Name". On the Contacts layout, address fields are in a portal of fields from [Companies] and will auto-populate based on the entry in "Company Name". HOWEVER - a few of our companies have multiple addresses. Ideally, a user would select addresses from a drop-down menu that would auto-populate the remaining address fields, but that's where I get a bit lost. Anyone have a more elegant solution to this, or can someone point me in the right direction on how to do this? Thanks!

hi' i have a portal in my jobs database that lists sale items(line items table). i wish to be able to create invoices from the jobs db in a seperate invoice db. i can move the customer data (from the jobs table) no problem but when i try to move the portal data by means of a looping script (if...set field(jobs_db to field invoice_db)i only get the last line of the portal data. it is creating a record and then overwriting it with the next portal records data. so i only end up with one new record. how can i get the set field comand to realise it needs to write to a new record each time it loops through the portal lines in the jobs db ?. or is there another way round ? t…

Im looking for ideas to streamline a customer/stock/job/invoice type solution. My stock db contains every individual item of stock available. clearly there are multiples of same stock items. Item A serial no 1 Item A serial no 2 Item B serial no 1 Item B serial no 2 etc. When it comes to invoicing i would clearly rather have the invoice say customer .. buys 20 x Item A and 10 x Item B rather than listing each item one by one with its serial. However item serial numbers must be logged into the sale so that items can be tracked both to customers and removed from stock accurately. Im sure this problem has been addressed on many occasions. Im not lookin…

I am modifying a contact list for my company, and right now there are 6000 records already present. Right now as it is setup, when entering a contacts company, you need to enter the company and the address. Normally I would just have it set up so that when the company is selected it automatically would add the address. However sometimes there are multiple addresses associated with each company. So what I am going to try and incorporate is have two lists, one for the companies and one for the addresses. I am hoping to be able to filter out the address list based on the company. (I hope I have explained myself clearly) To add/confound the problem is that when…

Have used filemaker 6 for about 4 years have been relating different files by existing parcel numbers that are continuously imported from another system. Have used these numbers which have had leading zeros and alpha characters in some cases in filemaker 6 with no problem. I am now upgrading to FM 9 and neither 'text' nor 'number' is working in all cases Why did it work in 6 and not 9 and more important is there a easy way to work around this. Yes I know I have broke ever convention that experts ever recommended The bulk of my data is stored permanntly in a private vendors much larger system and I import that weekly to filemaker to add other information and utili…

Here's the story. My company is moving over to a slotting system for our warehouse. Anywhere where a skid of a certain product can be put will be given a unique slot number. The number system works as follows: Warehouse # - Row # - Bay # - Shelf An item in warehouse 2, row 6, bay 15 (this means 15 bays down the row), and on the second shelf up will have the ID 2-06-15-B (Lowest shelf is on the floor (A). Highest possible shelf is 6 high (F)). Some of the employees would like to have a graphic representation of the items in any given row. Here's the solution that I've come up with so far. Imagine a grid. Each column is labeled with a number, …

Hi, I'm still in the process of developing an address/contact database based on the Business Productivity Solution from Filemaker. Most feelds and features are the same we need. What I'd like to change is the way of selecting the company for a contact: originaly it is a text feeld so I do have different typings of the same company (Arri, ARRI, ...). I changed this, to use a relation to a second company name database and open a drop down list in the contact db. But with a lot of companies, opening ths DD-List takes up to 50 sec. in iwp. A suggestion is to open the company database from within the contact record and select the company the contact works for. B…

I have a company table that has a relationship to a "tickets" table. I may be approaching this wrong, but the way I am using the "tickets" table is for several different items. A ticket could be a proposal, service order, invoice, or PO. I have a field in the tickets table that is for this purpose. I thought this is a good approach for me because the user could simply "convert" a proposal to a service order and almost all the data is carried over without having to move the data to a Service Order table. However, in my company table I want tabs for the user to view a portal of Proposals, SO's etc. I have created a new TO of the tickets table and defined multiple crit…

I have a bit of a problem. I have a relationship where I am trying to detect whether a child was modified through the parent. Kit (parent) KitID KitDescription CreatedOn ModifiedOn Component (child) ComponentID ComponentDesc KitID ModifiedOn In this example I can have an established parent and say 3 children. Through a nightly process a child may be either added or removed. If I were to run a search for records modified I would want to pull up the parent that was modified via its children. Any ideas how I would set this up?

I have a multi user solution that is shared over the network. I have a global field I'm using as a filter for assignments portal. This filter field has a workers value list attached to it. So by choosing a worker in the filter field, only the chosen workers assignments will appear in portal. This all works great. My problem is, Joe is one user and Harry is another. Joe will set this global field to see only "Joe's" assignments. But Harry wants to see his assignments. The way it is set up now, Harry and Joe can not filter portal at the same time, it is only one or the other. I need to be able to allow users to set their own filters simultaneously across the …

I am a newbie. I have gone through some training on the VTC website and a Missing Manuals book, but am still struggling to learn FMP. I am creating a home grocery inventory/list generator. Here is how I have it structured and the part I am stuck on. The main layout is Products, which allows for creation of records for any grocery item with a bunch of detail information. The two main areas are Product Info and Store Info. The Product Info has the name, category, sub-category, and description. The category and sub-category are referenced from another table used to set these. This section works fine. In the Store Info are the following: store, manufactur…

Hello again, I have two tables, contacts and mailouts, in a small marketing solution. Contacts can be either 'send invite', 'send press release', 'send call for submissions'. They can also be several at once. On the mailout layout I would like to be able to select to which (or groups) the mailout should go. These records should then show up in the portal. Ideally, if I tick e.g. 'send invite' and 'send press release', both groups should show up. I can't figure out how to design the relationship / calculated key fields. Any help much appreciated.

I have a portal showing: Partnumbers, Stockstatus, last invoice date Ok, i've managed to make a filter that let's me toggle between in stock/not in stock (value list), works great. Now, I want a filter giving me the option of showing rows that has been invoiced (has a invoice date) AND is "in stock" or "not in stock". I have a invoice table containing the dates, I have a order_row table containing the partnumbers and I have a parts list table containing the in stock/not in stock data.

This is my current table structure that surrounds the problem I can not figure out: Vendor Companies------Reviews------Criteria A "Vendor" can participate in many "Reviews", a "Review" has many "Vendors" particpating. A "Criteria" item is selected for many "Reviews", a "Review" has many "Criteria" items. A Review is a process that determines: 1. What Criteria we want to evaluate or compare Vendors against 2. Which Vendors we want to Invite to participate in the Review I can build a "Review Criteria Form" that all of the participating Vendors will be evaluated against, however, I do not know how to build the "Review Criteria Form" in which I can sele…

I have program that tracks student progress. There is a portal that has, among other things, a date with his current status. The portal has a date with each status, up to about 10. How to make a report that shows the name with only the current (highest date) status from the portal and not all of the related contents of the portal? Thanks. - The relevant parts of the tables are: Main table fields: name main_key ---- Child table fields: status_date status_type child_key fk_main_key

Hi World! I'm a beginner with FM, so I'm having several trouble. I have created 3 different tables with relations between them as they share common fields. My plan is to make a layout with "loaded" fields from other tables. So I created a new table with the fields I want to load into the layout, but I don't what to do to load data from other tables. Firstly, I simply defined the fields (double click on the field - Specify Field) as the original field I wanted to load but nothing... : I've tried the "lookup" option, and nothing. I really don't understand very well the relation and portal subjects. I've been researching through internet, FM help and…

I want to build a table which "loads" some fields from other tables and has its own fields too. I don't want to duplicate this fields, but I don't know how to do it with relations. Is there any way to do this? thx

Hi there, I've got a many-to-many relationship, via a join table, for two databases: 'People' and 'Campaigns'. Any individual can have many (marketing) campaigns directed at them (between 1 and 10), and any campaign can be sent to many people (between 5 and 500). I've got the people showing up in the campaign window by way of the join file using a calculation, within a portal. (The join file has a calculated field called calcName=Name and it's this calculated field that's shown in the portal in the 'campaign' window.) All good - but there are some fields that are in the portal that i would like to edit right there, and not go into the people file to do so. A…

I'm trying to create a database where there are Contacts and the Tasks associated with each of them. Right now on my Tasks Layout there's a portal which will hopefully be able to display the contact name. What I want to do is to go to the contacts layout, click on a button next to the particular contacts name and have it associated with the Task. However what I have managed so far is for a new iteration of the contact to be created as well. I've tried storing the contact name inside a new field and then transferring that to my portal but that hasn't worked either. I hope I've explained my situation clearly, any suggestions?

In the conditions in my relationships i need to have a logical OR beteew then instead the default AND that always shows UP. My situation is this I have records with two fields with two diferent dates and i need to make then apear in a portal when the condition based in the dates are true. So i set two dates a MAX and a MIN then only the records bettew it shows up. It works fine when i want it show records that macth one condition but with two and if i set equal max and mins one date or the other might be false making all my realation false since each condition is linked by and AND. So is there anyway to change the AND to an OR or do somthing equivalent.

I have a big problem and the most headache heading is the retional relationship. I want to use a gobal field with value "1" and link it to another table with a caculation field. I want to show all the record with value "1" in the foreign table. It is that simple. It suck as filemaker doesn't allow this simple things as the field is a caculation field and can not be stored. Anyone can solve this prolbem, it consume me more than 30 hours.

When building a database schema, is it kosher to create loops using table occurrences? Loops are illegal for good reason. It seems like it would like cheating to use a another T.O. to circumvent this restriction. I feel like I'm breaking some fundamental db design rules. But it's just so easy. The reason I ask is that in a larger project I find myself relating almost every table to eachother, just because I can (and it makes some obscure layout easier to implement). She the attached RG screen grab for a simple example. [Accounts] -> [Locations] -> [Contacts] (all are one -> many ) simple enough.. A contact is a grandchild of an A…

Apologies if this has been answered, but I can't seem to find it in a forum search. I have a nice sized database with about 10 Tables and probably 50 table occurrences. Everything works fairly well. There is one thing I've never been able to well, and it seems so basic. Is there an elegant way to populate foreign keys (aka match fields)? All my primary keys are standard incrementing integers. Most of my relationships are based on said primary keys. That means than when I'm trying to relate two records I have to select from a field (or value list) of integers - not very functional for my users. I DO use value lists with "also display values from second …

I'm revising a database that stores information about pieces within an art collection. We store information about the dimensions of each piece within the collection. Each piece within the collection can consist of one or more components (up to 3 components per piece) and I need to collect dimension information for each component so that the space requirements for the WHOLE piece can be estimated while at the same time it can report on the individual dimensions of each component. Each component, depending on the piece, can have 2 or 3 different dimensions of 4 different types (height, width, depth, thickness). The way it is currently done is by having a Piece table and…

I have a several source tables (VendorA, Vendor B, VendorC, etc...) having the same fields: ProductCode, Price, etc... all linked to a Catalogue table by ProductCode. Catalogue table has similar fields: ProductCode, Price, etc.... Price in Catalogue table is looked up from one of the Vendor tables. A product code is unique to each vendor, in other words, Price in Catalogue comes from 1 table and 1 table only. Currently I have set Price field in Catalogue as calc with the expression: Case (IsValid(VendorA::ProdCode); VendorA::Price; IsValid(VendorB::ProdCode); VendorB::Price; etc...for all the vendors). I am wondering if there is simpler way/formula to pull the …

I'm having a bit of an issue with field input to lookup and change on it's own (Via table ref) Basically i have a layout that creates new jobs. It asks various questions like: - JobID (string) - CustomerID (references another table) - Description - amount The problem is with the customer id input. It was originally a pull down select using a list, which for me works. But the users know all the customer codes (2 digit), and would prefer to be able to tab thru all the fields and just enter the code (faster for them) Now the problem comes in here. The Job table has a column "CustomerID" which references Customer table. But the customer table…

:bang: I have 4 seperate medical record tables in my database. All have the same fields and keys - were intended to track specific conditions (chronic vs accute, for example). These all display in their own portals. Now, the boss has decided this is too complex, and wants to combine the data into one table. I'm new at this ... struggling ... Is there a way for me to create this new combined table without exporting the 4 existing and then importing them back one at a time? Thanks.

Contacts DB with additional Portal on Contact Record showing a list of Appointments. The Portal shows an identical list irrespective of the Contact Record being viewed. This works well but I would like each Appointment to show automatically on the relevant Contact Record via another portal or whatever. To sum up, an Appointment shows on every Record until it is deleted but also shows permanently on the relevant Contact Record to which it relates. I haven't a clue whether this can be done - help.

Lets say I have a portal showing invoice numbers and invoice dates. Above this portal I want a popup menu saying: Show invoices: "6-12 months", "0-5 months" etc How can I filter the content within a portal? Another example would be a check-box [ x ] Show items in stock only suggestions?

Invoice_analysis http://img519.imageshack.us/my.php?image=bild1vo9.png I have a tricky one that I can't seem to figure out how to solve. First: I have a table with invoices and invoice totals Second: I have a table called invoice_analysis where I group say 10 invoices in a shipment, ad a freight total for that shipment, in order to calculate margins etc. Here's the relationship: http://img261.imageshack.us/my.php?image=bild2od0.png Yellow box: Invoice_header contains date, total sum etc it has a relationship with "Inleverans_rader" wich contains the individual items on the invoices. This table is not relevant here though "Fraktanalys_h…

I have a group of approximately 15 related files that I use to track job related time and expenses. There is one main file which displays most of the data, while the other files are primarily line item file. I created these files about 12 years ago and have been using and upgrading since then. The files are currently in FP8. I am getting ready to add additional functionality and also update to FP9. Is there any reason for me to consolidate the related files as tables in the main database file or should I continue to use them as related files? Is there an easy way to consolidate related files into one main file and at the same time retain all the data? Thanks, …

I am creating a database to enter timecard information. I have a table for the timesheet and a table for user account creation (for use by my supervisor for creating new accounts w/o knowing the program) the account table creates accounts and store user/pass info. I want to have individual accounts for each employee so they can only access their timecard info. In the timesheet I have the FM 8 get the account name of user and assign it to their timecard. I can get the account name into the employee field, but I want to have FM 8 replace the user name with the cooresponding name of the employee. It would seem that I could do that with relationships, but i do not k…

Hello, not sure where to post this question. I have a couple different business. I would like to use a single fmp file to run all of these businesses. I do not want Company-A to be able to view Company-B records when logged in as Company-A. Not sure how to set this up or even if it can be done. Primary reason I wish to do this is... I'm in the stages of building a complex project management db. I'm using this db in "real-world", actually creating and reporting actual business records. And at the moment I have one company on old solution, and another company on the new solution. As mentioned, Im still developing the new solution. So I don't want to create c…

Hi Everyone, First off, great community. I'm a newbie and I've been making the transition to FMP from Access and I certainly have enjoyed the change. The solution I'm putting together is coming slowly... But I have run into a few snags: I have a table called TBJobCost and this has two TO's on my graph: TOOutsourcingCosts and TOTimesheets. I've built layouts that add records with respect to these occurrences. However, when I create a list layout for TOTimesheets, I get all the data in the TBJobCost table. So, what I need to know: 1. Have I used TO's correctly (Timesheet data and outcourcing data is almost the same hence using the one table)? 2. Shou…

I have 2 different tables. I want to display a calculated value from table 1 in table 2. I first did this by making the value in table 1 global. Worked OK, but the value did not update correctly. When i made it non stored calculation, I could not show the value because the tables were unrelated. I have nothing to relate between the tables. There is no common denominator or anything. The calculated value in table 1 is a get(TotalRecordCount) calculation that i want to display in table 2. How?

Hi Guys, I know this must come up every other month, but I'm struggling with how to filter out duplicates in a particular portal. The relationship is: [Clients] can [Purchase] a [Title] 3 tables, purchase being the join. In the [Title] table there is a field called interest area. I want to see, from the context of an individual client, a distinct list of interest areas from books they have purchased. File attached. I've tried working through some of the other threads like this one: http://fmforums.com/forum/showtopic.php?tid/194751/post/288040/hl/distinct/fromsearch/1/#288040 but it's just not clicking how I can use the self-join technique to …

I have three tables, table 1 has fields on Merchant and their info, table 2 has fields on Orders (Order Number, Date) those are visible in table 1 in a portal, table 3 has the ordered products that are in table 2's portal. my question, how would i link and add them together to work and how can I make table 1s portal display the supplier related orders?. Please see file. Full Guest Account! As usual thanks to you guys! Clone8gv3.fp7.zip

ok. i did some filemaker programming long time ago, when there weren't any tables. now i'm forced to do it again. this time on filemaker pro advanced. so here is the problem. i created a table in the sam e database. it showed up as a separate layout. in that table i'm storing 3 fields. one of them is to bind the original table with the 'secondary' one. the fields in the secondary table are presented in the main one through a portal. and in the relationship the option to create files in the secondary table from the main one is turned on. now what happens is that even that there are no records in the secondary table there is already one record that shows up in th…

I recently moved up from v7 to Advanced v8, and have the latest v8.0.03 update. - Try as I may, I cannot seem to reproduce the behaviour that I am used to when adding a record to the last (vacant) row of a portal to a related table. - Whenever I try to add, I get "This field is not modifiable". I can add and delete records at will if I use the actual table's layout, just NEVER through the portal. The relationship is set up to allow creation of records, but it doesn't seem to work. - Help, please ! - TIA - Chris

I have two DBs: RFQs and Parts. The RFQ database has these fields: Part Number, QTY Needed, and Part Description. The Parts database has these fields: Part Number, Part Description. I have a relationship between these DBs based on the Part Number field, with allow creation of records in this table selected. When I create a record in the RFQ DB, I enter a Part Number along with other information. I want the relationship to look at the Parts DB, and if the Part Number does not exist, I want a new record created in Parts with the Part Number. However, a new record is not being created. What am I missing here? Thanks!

Hi. New to Filemaker (but not databases in general) so forgive me if I fumble a bit with some of the FM-specific terminology. I have a design issue that I'm not quite sure how to best address in Filemaker: I have two different types of contact tables in my contacts database (one table for businesses; another table for individuals who may or may not be affiliated with one or more of the businesses in the businesses table), which are almost entirely different in structure, except that they both have a notion of "location", based on an address (which is part of the table definition for each of the two contact tables - i.e. each contact table has its own specific…

I have a database containing order data. I would like to have another table that would have: WeeklySales Date right now I calculate the weekly sales figure in my sales order database. But when I export the numbers I end up with many duplicates of the same info. The idea here is to create a table that I can use to make charts easily. Ideally in FusionCharts. Thanks in advance for your advice

I have two tables. One has an auto-enter serial number that is a number, but I entered 00000 in the serial number box, so the serials all have leading zeros -- 00001, 00002, 00003 etc. Another table is related to this table, and I store these values as foreign keys. When I do this (using Set Field), the leading zeros are stripped, so I end up with 1, 2, 3... However, when I first set up the solution, I updated the old data using Replace Field Contents, which kept the leading zeros. As far as I can tell, since both fields are numeric, the relationship works, but having a mix of foreign keys with and without leading zeros worries me. Should I care…

Is there a way to reduce record numbers using relationships? I have been using scripts to reduce UPC (universal product code) data. This method is cumbersome to manage across many databases. Data: Garment styles and associated attributes for a product cart. The original data contains one record for each style, color and size combination. I want to convert this information to one record per style. i.e., each style record contains a list of colors and a list of sizes as attributes. Example: Convert Style X, a size, a color (5555, S, Blue; 5555, M, Blue; 5555, L, Blue; etc.) To Style X , sizes, colors (5555, S M L XL, Blue Green Red) Is there a ea…

I have an order with many types of equipment, but each equipment has many parts. What should I do in order to display the equipments with related parts on the order layout? I have trying to solve the problems for a while but I am still very confused about it. Could anyone help me and explain it to me by the sample that I attached? Thanks. One_to_many_to_many.zip

Greetings all...I am creating multiple records in a table (call it Techniques). Each record has a category field that ultimately appears many times. The category field actually represents the types of techniques that are used in sequences in the motion picture visual FX industry. Trying to figure out the best approach to setting up a (summary) portal that will show the category appearing just once. Additionally, here's where it gets a little tricky. Once I have set up the portal showing each category once, I will then need to calculate a range of factors, including: how many times (a count) a category appears in the Technique table's records (the amount of times that…

Two tables, Table_A (parent) and Table_B (child). Table_B records display in a portal of a Table_A layout. Match field is A_ID. Table_B should have field B_ID such that A_ID & B_ID together are a unique key for Table_B. I want to automatically generate B_ID, and I want it to start over at 1 for each parent record, and increment for each added child record, so that, for record 1 in Table_A, the related Table_B records have B_ID of 1,2,3..., and for record 2 in Table_A, the related Table_B records have B_ID of 1,2,3... etc. The problem: A portal row doesn't commit until the parent record commits, so you'd have to type in a new portal row, then click outside the por…

What I am trying to do is create a list on the left side of the layout(a portal)of all the invoices of a specific customer. The right side will show the preview of the invoice chosen from the list. This is done by choosing the invoice you want and setting the invoice number into a invoice global field table and using lookup to populate the rest of the info into the global field table. All of this is located on a "Invoice Tab" which is on a layout based on the Customer table. Ok, now the problem is that once the info is in the invoice global field table and it shows up on the right side it wont show any of the line items in the portal based on the relationshi…

Using Portal to display tasks on Employee form. A script finds Open or All Tasks using a Relationship between global and calculation fields. Works fine. However, CANNOT Add new Record from Portal -- error "This action cannot be performed because this field is not modifiable" -- even though a = Relationship is set up between EmplID fields & Allow Creation enabled. Both Relationships discussed above are defined in a single Relationship link. Research on this Forum indicates it's the relationship of the Global and Calculation field -- didn't understand it entirely -- I guess because the Calculation is a concatenated value that is not = to the Global fie…

I want to start keeping track of whenever a customer in our database gets a particular mailing, whether it's a mass e-mail, a mailed brochure, or a PDF copy of one of our publications. At the same time that I export the mailing list or send the e-mail from the database, I want to add a record of them receiving that mailing linked to each record in the list. As I see it, it's just a matter of creating a related table with a primary key, a foreign key (for the customer) and another foreign key linking to a third table that stores the text of the e-mails or descriptions of the mailing contents. I was just wondering if there was a better way to build this than to ha…