Relationships

Hi, If I have 2 item numbers, one is a new item, the other is the same item number with different packaging. How should I deal with it if my client still want to keep track of it in the future. Right now I just add "_old" to the old item. Is there a better way? Thanks. Kent

I can create child records with a portal with no problem. But I would like to do so without using a portal. Is there an easy way to do this?

Hello all, I'm working on a new database solution for the city's inspection department, and I've hit a bit of a snag. Untill now, all of the departments data has been in numerous flat files, no relationships whatsoever. I've been reworking some of the files to include relationships, and have added many features that speed the work flow. On this new solution, I'm trying to figure out how to build more functionality into the violation tracking and letter writing features. This is a completely new system for a new department, so I have the ability to start from scratch. I've hit a snag on how to structure a portion of the system. Here's the basics:…

I searched for this one - I know it must have been asked before, but no luck. Chances are, I'm not using the right terminology. I have a file of about 10k dwelling units that need to be tracked for yearly inspections. Each dwelling unit has a unique license number which I have used as a keyfield. I just realized that I need an additional, more permenant number for the key - the license number will change if the property is sold. So - I need to create a new serialized dwelling unit ID number. My question is: how do I create this field so that FM will automatically populate this field, rather than having to manually enter the numbers? Thanks in adv…

I have two Databases Call them Data1 & Data2 -- both are hosted on FM11 Server. I have made a pulldown menu on Data1 layout called Carrier... It pulls the names off of data2. The relationship between them is Data1ID=Data2ID The Problem is when I make a script to go to the related record to see more detail is goes to the wrong record... It goes to the ID that I am on not the name in the pulldown field Any help would really help... An example of how this work would be very helpful. Thank you everyone

Not sure what to call this type of relationship but I have companies that are affiliated with one another an they want to view all affiliated companies from any of the affiliated companies. So If: Company 1 is affiliated with Company 2 Company 1 is affiliated with Company 3 Company 3 is affiliated with Company 4 Company 4 is affiliated with Company 5 Then they should all be considered affiliated with one another and you should be able to see a list of companies 1 - 5 from any of the individual company records. I have sorted some of this out using a join table that joins to its self multiple times but there will always be a limit to how distant the r…

Thank you in advance for your help. I'm planning a new database. transport of workers or staff accounts will be kept. Scenario as follows: In a factory or business personnel, suppliers of pre-determined routes, the company's own vehicles or vehicles with an invoice at the end of the month to be moved. Tables: 1-Customers CustomerID CustomerName Customer Address Customer Phone 2-Suppliers Supplier IID Supplier Name Supplier vehicle license plate number Supplier Wire 3-Tour Routes Route ID Route Name Route km Rota Price Now here is how you relate to the statements required to be established and where the inp…

Hi, I have a database of children in schools. Sometimes siblings should receive only one document in common, so that if one sibling has received it, the other(s) should not. The parents are not always registered, so that they may not be a common link. How can I proceed to make an easy solution to easily register a relationship between siblings? And in such a way that the relationships only needs to be registered once from one sibling.

My brain just won't wrap around this one. I've got students in one table (including a field for what "team" that student is on) and possible courses in another. Each student ranks his preference for the various courses and is ultimately assigned a few of them. I've got a join table with StudentID, CourseID, preference, and whether assigned. I do just fine with a report sorted by course, sub-summary by team, then listing the students assigned to that course who are from that team. But I get stuck going from the other direction: I want to show the student's name, each course they are assigned to, plus the name of any other student from their team who has also bee…

First time poster here - I've been building a database for my father's small business - it isn't easy trying to figure everything out on your own. I've finally given up and need some help. For simplicity sake, I have two tables that need a many to many relationship. Customers and Interest Codes. I've created a join table between them which I think is setup correctly but I'm not quite sure how to properly setup the a portal in my customers layout to show the interest codes attached to that record. I'd like the portal fields to use a popup menu to have the Interest codes as selectable, and I've setup a value list but I'm not sure if I have the right fields select…

I have a invoice database with a related table for line items. The line items are added by the user in a portal on the invoice layout. (via relationship "allow creation of records in this table via this relationship") The problem occurs when the user adds a line item, the script symbol comes up and it takes about 18 seconds! I have a script that basically duplicates the line item above. When the script adds a record it is almost instant. FYI I am running this solution on 3 machines with one of the machines hosting the database (no server software) Any suggestions

Ok, this is the situation. I am trying to create something fairly simple in the way of contact management. There are two tables, one for the business and one for the people who work in the business. I am trying to recreate a view where the user can click and choose the specific Company which will then populate the Contact field (this currently works) and from there fill in the remaining fields. The one *problem* that I have is that if I change the customer field it will NOT repopulate the city, state, etc fields for the changed customer name. Here is a simple screen shot and I hope I have been clear (new to the forums) and by all means ask if I did a horrible job…

Hi, I am developing a database. If my database has products and inventory and quote information, and customer, should i combine them as tables inside a database or create multiple database for each topic. Under what circumstances I need to develop 2 database for them to link to each other. Thanks. Kent

Hi there, I apologize in advance, I have a feeling that this is going to be ridiculously easy to do, but I can't seem to make the relationship work to do this. I'm creating a solution where police reports are entered, once a report is entered, an officer may want to cross-reference a report to another. It could be more than one report. ie. Officer A enters Report #1 for Robbery Officer B enters Report #2 for Vehicle Theft Officer C enters Report #3 for Abandoned Vehicle It is determined that all 3 reports are related (vehicle is stolen, bad guy robs a bank, and then abandons the car). If a user views any of the 3 reports, it should show the 2 oth…

I have a trouble of expressing a condition within the CASE statement. I have one number column "numtotal" with records numbers ranging from 0 through 27. I have a second number column titled "range" where the function happens. I'm wanting to range the numbers: 5 or less = "LOW" 16 or more = "HIGH" 6 through 15 = "BULLSEYE" I believe the "CASE" function is the right function to use, but I can't seem to figure it. Help.

I have a filter which I can't get to work because of indexing. Tables - Artists - Artworks - Invoice - Invoice Line Items The calc is in Artworks: [color:blue]StatusCalc = [color:red]If(Sum(Artworks_InvoiceLineItems::Constant) ≥ "1"; "sold"; StatusTxt ) Status = StatusCalc &"¶all" In Artists there is a portal with all of the artworks (all; sold; available) The join is kpArtistID_kfArtistID gStatusJoin_Status The problem is that Status cannot be indexed. How can I get around this?? TIA

I have FM Server 11: Hosting two (2) FM Databases. I would like to create a 3rd Database the will hold information that both databases can use. This way I only need to update one database and the other two (2) will have the same info. Name of New Database: Carrier_info Simple items like company rating etc... Questions: 1) How can I go to related record from Database 1 to the new database? Go to related record only seems to work when going to a layout on the same database. I would like to be able to open the related layout to see more info from either databases. 2) Also, if I create a new database (Carrier_info) and link the others to it -- will …

Every now and then I need to stop by here and show my ignorance of basic database workings. One of my hobbies is statistics. Now that my daughter is playing volleyball, I thought I'd set up a place to track performance. My comfort zone is Excel and that has worked very well, but I know spreadsheets are fraught with peril. As I started implementing my spreadsheet in FMP I eventually realized that I goofed in the basic set up. I put all the data values into the "personnel" database. That works great for 1 event, but I obviously will need to put data in for many matches. Each match has 2 or 3 sets with data for each set grouping into a match. Then I would also …

I know there is several ways to handle this -- I am looking for best examples and why for moving field data from one layout to make a new record on a different layout. I would like to physically copy the data... in about 17 fields Should I use the set field with variable function or is there another way to move this much data?

Table1 with Field1 and Field2 uses Layout1. Related Table2 uses Layout2. After finding a select number of records in Layout1, how can I show (and use in an email) those found fields in Layout2? (My database is more complicated, but simplifying it as above will hopefully suffice for this newbie question.) Thanks in advance.

I am new to FileMaker and databases in general. Hopefully this is an easy question. I have a database with two tables, Customer Contact information and Invoice. I have a lookup setup to pull over Customer Info , Customer Number, Ship to Address from the Customer Address table to the Invoice. We want to be able to modify the Customer Ship to Address on the Invoice without it changing the info in the Customer Address table. Right now it is pulling the data over based on Customer Number but when we modify the Ship to address it is also changing the source address as well. Thanks, John

Is it possible to display in a portal - Parent Record with Children Records below it? For example: Parent One ---Child One One ---Child One Two ---Child One Three Parent Two ---Child Two One ---Child Two Two I have seen some articles on how to do this with ONE table... but not TWO tables involving Parent and Children. My ultimate goal is to build an "Overview" portal on the Parent layout so user could see Parent and Parents children on the fly vrs. generating a report. Used as a navigational tool as well. I already have this built showing only the given children records. I could just show the related Parent in the same row... but it look…

I have a database of Theatres, and by relating a table by City and Theatre name I am able to call up data in a portal for each time a tour visits that venue. Sometimes, however, the data in some parts of the venue needs to be called from a different record (Cities have festivals which are administered by a whole different set of people). I would like to still keep the data from the City & Theatre, but somehow manipulate certain records to show from the parent of the festival instead of the Theatre. I can add a trigger for this in the form of a tick box, or a script button, but I am now stuck as to how to call the information from the two separate records. Any adv…

I have a table entitled ACCOMS (for accommodations) which has the fields #rooms, #nights and rate. I have a table entitled Hotel. I have placed a portal from the ACCOMS Table to the Hotel table. No problems there. On the Hotel Table I have a field named Total Revenue. I would like to place the total revenue of all the accommodations for that day in the Total Revenue field. Right now I have it set up as a calculation field with the following string; ACCOMS:#Rooms * (ACCOMS::#Nights * ACCOMS::Rate) This is entering the total revenue for the first portal entry only and not a total of all portal entries. When I try repeating this string wi…

I am trying to build a Procedures database and I am stuck on how to handle the sequence of ACTIVITYs or sequence of ACTIONs. Tables: PROCEDURE, ACTIVITY, ACTION, ? Each PROCEDURE is completed by performing one or more ACTIVITYs Each ACTIVITY is completed by performing one or more ACTIONs Each ACTIVITY in a PROCEDURE must have a unique number representing the sequence (or order) in which the ACTIVITYs are to be performed. Each ACTION in a ACTIVITY must have a unique number representing the sequence (or order) in which the ACTIONs are to be performed. Sample Procedure: How to Bake a Cake (PROCEDURE) Step 1: Obtain Ingredien…

Greetings. I'm mapping the network for the school I work for. Each room in the school has one to two jacks. Each jack connects to one socket on a patch panel (in a room). Each socket on the patch panel is patched into zero or one ports on a switch (also in a room). I was thinking I would link a jack id to a socket id to a port id. This would let me see where a node plugged in and where on the switch it was connected. But I don't know how to link these tables together. And I'm not sure how the interface would work: drill down to select the jack, drill down to select the socket, and drill down to select the port. Then join them. Multiple, scripted Finds?…

Copy field data into a join table field I am having trouble trying to paste field data into an ID field in a join table. The DB works without a problem but I cannot past the ID into the join table field after performing the find on the desired records. I tried Replace Contents (thanks weebick) on the found set, which seemed to work but, in actuality, did nothing. Tried set field via script manager but the calculation setup says the table cannot be found even thought the many to many relationship via the join table works perfectly in the system. I can do a control paste into the join table id field and the related record loads beautifully but doing this 6000+ time…

How can I have two related Tables (Statements and Orders) so that a payment and a refund (two different records) in Statements will populate just the one record in Orders? The Tables are related by the number generated automatically in Orders. The number is entered manually in the relevant Statement record. The idea being that when the payment and, if applicable, refund appear in the Statement, they are numbered and will show in the Order record in order to validate the item and complete the Order. I can make it work with a payment or refund but not both. Can I do this?

Hello, I'm having an issue with some related records and an email script I built in my DB. I have a basic table that has all Job information. It is related to a table that has equipment information specific to each job. The two tables's are related via a serialized field "Job ID" The problem is this. I want a form email that is populated from the Job database that also includes a list of all equipment from the related table. I can get it to populate the email with information from the first related record easily, but after that I am lost. I've done quite a bit of reading on various forums on similar topics, but most approaches to similar issues see…

Possibly. Always hard to know exactly which forum to post such. Im looking to get insight/impressions by others. Not a newbie, but not an expert like a bunch of smart folks here. Im updating a database we use to keep track of serial numbers for products we sell, and in addition we receive some of these products back for service. Its useful to keep track of when/how many times a product has been serviced. The service database is separate from the serial database. Right now, we have separate tables to track each different product serial number - for example, widget A starts with serial # 1,..., widget B starts with serial # 1,... In retrospect, we …

I’ve completed a migration step where I need to find transfer records in an Accounts table (it holds a lot more than transfers) and import them into a new Transfers table so they become the many side. I then save only the first ‘parent’ transfer record (one each unique by Contract/AcctNum) and it must be the first AccountID of each ‘set’ because it holds history and we want the sequencing of that AccountID where it is within the Accounts table. I delete all the remaining same Contract/AccountNum records out of Accounts and zero the amount but I first must point the new transfer records back to their NEW parent (the first) by reassigning the AccountID in the transfer tab…

Hi. I am having a problem working out how I should configure a relationship and making it a multi-user affair. (Sounds like family issues doesnt it..) I have spent many hours on this going in circles and am little the wiser. Offering money or services to whomever can help me may be wrong on this forum may be wrong and I withdraw this paragraph if so, but I am happy to engage somebody to get over this hurdle for me. In short. I have two table I need to populate. Once is a table full of items - lets say stock with item description and pricing and the like, and the second table is one with quotes for clients. My current method (although not working), is to…

I currently have an employees database that holds information containing employee information, departments, office locations, and managers. I need to segregate the employee information by type of employee such as full-time employee, temp employee, contractors, and interns. I want to be able to create layouts for each type of employee and also be able to create an entire list of employees. I'm not sure if I should set them up using different tables and relationships for each one or not. Any suggestions on how I should go about this?

Suppose I have have two fields: "double" and "half". For a given record, the value of double will be twice that of half (e.g. double = 4, half = 2). Sometimes it will be convenient to enter data for half (e.g. 2), in which case one wants data to be calculated and entered automatically for double (e.g. 4). At other times, entering data into double will be more convenient, in which case one wants half to be calculated automatically. What are some approaches to providing flexibility like this in choosing among fields for data entry?

Hi. I'm in the middle of creating a database, which I made the mistake of thinking I was doing good on...until I got further into and realised I could be making major mistakes. I have attached a copy of an excel file, in which I have listed each of my tables and their fields as a reference. Thought it might be easier seeing it all laid out in front of you rather than looking at the disaster database I have created. Basically, I have clients (Table 1) which I need to record the info for Tables 2, 3, 4, 7, and 8 on. I thought I was to make the relationship to each by the client ID. Was I better off not seperating some of them into their own tables and just…

It seems like this must be very basic, but it has me stumped. I have two tables, agencies (kp_agency_id, agency_name) and contacts (kp_contact_id, kf_agency_id, contact_name). Each contact is a member of an agency. I would like to store only the id of the relevant agency in the contacts table (as shown above). When a user edits a contact, I want them to be able to choose from a list of agency names, but I don't want them to see the agency id. When the user changes the selected agency in the contacts table, I want contacts::kf_agency_id to change but I don't want anything to change in the agencies table. Everything I've tried either makes changes to t…

I have two related tables, one to many. NAMES, made up of: name home_address nameID PLACES, made up of: Place nameID The layout shows one Name and through a portal shows many Places. How do I write a button script that adds a new record in PLACES and then inserts names:home_address in places:place and name:nameID into places:nameID Thanks

I am looking at the Invoice example. The invoice can have many Product Items in the Portal table. but when you look at the table itself, not the layout then there is no such record. I have a very similar problem with purchase orders from vendors. where there is one order with many products. i need to store them as transactions and perform some calculations on the values. so how is that handled by filemaker??

I have a beginner question and have not seen an obvious solution on the forum, so if I missed it somewhere, please point me in the right direction. I have a 3 fields. First one you choose a subject, the second one populates the information that goes under that subject. Works great, but wanting another field to autopopulate a number based on the selection of the second field after it is chosen. I'm sure it is probably easy, thanks for any advice.

Hi there, I have recently gained a new client. The client is a Nautilus Gym in the local area. They wish to move from their current paper file system to a digital system encompassing FileMaker on both Mac and Windows computers and iPads for roaming around the gym accessing/updating their members data. I am stumped on this one tho. Member details and payment stuff is simple, all done on that front. But recording the exercise data is going to be difficult. These databases have to be quick, but they are going to be HUGE. For every exercise (of which there are 32 in total, not all used by every member, that would be Chuck Norris gym work!) I need to be able to log w…

Hi everyone, I might have a data structure issue here, since my search function does not work properly.. so here is what i have so far- i have tblHousehold and two baby tables tblBabies and tblBabies2, and these are self-joint with the indication that there are siblings who belong to the same household, so they have the same household IDs but different baby IDs.. I wonder if this self-joint structure is proper or not.. They have the exactly same fields, and only tblBabies links with other related tables such as tblLanguages and tblBabyEthnicity .. Any ideas? Thanks a lot!

Hi - have a simple race results tracker. An event could be a cycle race, triathlon or other. The results collected would differ depending on race type. I have created a results table for each event type, with a single table to record the results for the event. Can you recommend how I set this up so that depending on the event type, the correct results table is accessed. I only want to present the correct results table.

I have some related tables of categories of items that each have an item number. So there are tables Category1 and Category2. The Item Number in Category2 can be repeated in the whole set of records but I need to have a way of keeping the item number unique within the set of records related to each Item in Category1. In other words table Category1 has items 1, 2, 3,etc it is related to table Category2 with items 1:1, 1:2, 1:3 and 2:1, 2:2, 2:3, etc. The relationship, keyed together with auto entered P&F, keys works fine and I can display only the related items in a portal through the relationship of tables Category1::Category2. The item numbers are not the P&F key…

Hi. I am having a problem getting my head around the best way to achieve a certain job. I need to be able to have drop down boxes on fields that bring up only information related to the previous field, thus drilling down to find specific items. I'll keep it as succinct as possible. I need to draw up quotes. I need a table to have a list of items. For this example, I bring up "Car" in the first field, "Red" in the second field, then "Cherry" or Fire Engine" in the third field. Assuming that the only item in the first field that has "Red" as a subsequent choice, how do I limit the second, then third (etc) fields to narrow down what can be selected. What woud b…

Hi all, been awhile since I've been here but it is good to be back... I am trying to figure out a simple calendar solution with your basic month, week & day views as well as with appointment functionality. As you might have guessed by now I am hitting a wall with the last part, appointments, what i am trying to do is to have multiple appointments per single day and also to display first 3 appointments on month view via portal or any other way. I got as far as displaying of the appointments but it displays all of them instead of displaying only the ones for that particular day in question... I checked around and have looked at some of the available …

I need to view related fields from Microsoft access in our filemaker database. The access data will go into our report that will be exported or printed out. I have a unique identifier to relate the two databases but I have never related a non-filemaker file before. Just a side note that I did not design the access database and am quite unfamiliar with access in general. Thanks very much in advance.

I was thinking of implementing a separation model for my FM Go solution in order to only allow optimized layouts and scripts when in FM Go. Since my main database's relationships look like an insane workaholic spider has gone mad, can I somehow use those relationships in a separate database or will I have to painstakingly redo the entire relationship structure? Thanks, Peter

I am working on a system that requires an extensible representation of human relationships, i.e. Families. I have created, in the past, both models that use Contacts joined via relationship(join) records and direct key based relations: MotherID, FatherID, SpouseID. Both can represent very complicated relationships. The schema for a join record system is very simple but has high maintenance issues because of the double joins, e.g. Father-->Son, Son-->Father. The direct key way is neat because with just a few bits of information I can discover any related contacts by identifying how two contacts share keys, but the schema gets large representing the many kinds of rela…

Hi, I've been searching for an easy explanation of context and related tables. Any help would be greatly appreciated. Thanks, Skip

I've struggled with what is likely a very simple issue now for days and am hoping that someone here may have a few minutes to assist. Screen grab below is a simplified version of basic relationship I'm struggling with. I have a match field (FID) in the 3 sub-tables to the left. On creating a new record in any one of these tables, I want the table I am working in to: 1)automatically create a unique serial number in the FID field in the Feature Index table on the right. This a serial number/match field 2)via a relationship, have this serial number in the Feature Index table inserted into the sub-table that called the new record. Does this make…

I have a small cross-country running database that enters a runner's number and then displays the time taken for the run. Currently I can only see the last entry made and I would like to see a list of the last 15 or so entries made. I know it has something to do with relationships..... can anyone help please?

Hi All, I am working on a database which reads call records from a pabx and splits them with associated costs to separate people. I have one table callrecords which holds all the records, the only thing I need from there is the duration and destination number. To determine the charge for the call I need to lookup the call rate from my rates table which defines the prefix for the number and the rate to charge for the prefix. My problem is that I want to setup a relationship to do this lookup of the call rate, but, different countries have different prefixes, for example US is 1, but Finland is 358, Germany/Dusseldorf is 49211, how on earth can I match the call rate …

Trying to simplify customer information Retail_Invoice.zip

Hi, I’m having difficulties creating a database to catalog workflows. I’ll try and explain. The workflows are made up of various components (these individual components are in 1 table). The workflow is then in another table and the components are added via a join table and viewed in a portal. This way we can see all the individual elements that make the workflow. Also in the components layout there is a portal listing all the workflows the component is used in. This is important so that I can see the possible knock on effects of changing any one component. The problem is, that a workflow might contain another workflow as part of it. What I need to be able …

After many hours working on that problem, i'm here in coummunity. As an attachement, you find my data base. First of all, in text below in brackets I write the german name; my data base is in german. In attachement is also a picture, this shows my problem zone. In portal 1, I put in the preposition (Zubereitung) steps with all the ingredients (Zutaten). You can see, that Pos. 1 and 2 have the same article (Zutat), it's "Olivenöl". One time with mass 5 (Menge 5), one time with mass 2 (Menge 2). In portal 2, under portal 1, there should be a merged ingredient line with the same articles. In this example there should be in only one line the article "Oli…

I am working on a clinical trial database. There are four tables. Two of them are TrialTitleID and another is InstitutionNameID and both will have primary keys. But these two tables are many to many, i.e. one institution can have many trials and a trial can have more than one institution that runs the trial. I can't figure out a logical way to have an intersection table to make these each be one to many. Any help is greatly appreciated.

I have a multikey relationship that links one person to another with a join table. The join table has IDs from two records in the people table. This allows me to link everything just fine, but the only way I can currently display only one of the people in the portal is to use conditional formatting to blank out the information for the record being displayed, which means the portal shows both ID records from the join file, but has one conditionally blanked out based off of the current record. And that makes it impossible to correctly sort. Is there a way with a multikey relationship for me to set up a portal that only displays the record that isn't the current record?

What is the 'x' option for associating the 2 fields in a relationship? Can't find it documented and it wasn't obvious what it did when I used it in a test relationship. Thanks in advance.

Hello all, I'm a newbie to filemaker and have run into some problems. I'm trying to create a project database that will allow me to track projects, clients and send invoices. I have 3 tables; projects, invoices and clients. The problem I have is that I want to link a client to a project but I also want to link a client (I don't always invoice the same client that I do the project for) to the invoice. Curently I have a many->one between projects and invoices and a many->one between projects and clients. Without creating a new table for clients I invoice, is there a way to have different clients on the invoice i.e. One that the projects are linked to and one wh…

I have 5 tables contacts groups groupmembers events donations groups can have contacts associated with them (through the join table "groupmembers"), and they can have an event associated with them. donations have a contact associated with them and can have an event associated with them. I have all these relationships set up and working, but the following relationship is giving me trouble. On the donations page, I want: A. to show all groups that the contact related to the donation is a member of. B. ALSO, groups should only show if the event related to the donation matches the event related to the group. I've been able to create…

I thought I was doing the right thing, now I'm wondering if I've led myself down the wrong path. I have a very large table in my file. I have created a field to indicate which of those records I want to use for a specific purpose. I was hoping that a self join on a second table occurrence would do the trick. No luck. In my sample file below I have one table that occurs twice. The "value" field is what I'm starting with. The "calculate" field has the calculation. In this example I'm returning the value of "value" in calculate if the value is greater than 10. The second table occurrence is called "testing 2". The layout for "testing 2" is "testing…

I have two tables: Customers and Orders. The products that can be on orders are: Widget A and widget B. Each kind of widget can only be ordered once per customer. The order status can be backordered or shipped I need a report or export where each customer appears only once, in a single row, and with a column for each of the Widgets ordered. Like this: Customer, Widget A Status, Widget B Status Joe Customer, backordered,shipped I have tried everything I can think of: o A calculated field for the status of each widget in Orders o A calculated field for the status of each widget in Customers o A report or export with grouping Every time, f…

Hi, this is probably pretty simple but I would appreciate some direction on how best to approach this. Join table? Some other method? I have a list of fields that will be used for Budget and Actual costs for each inventory item (these are real estate assets). The fields will be the same, except one occurrence is budget and the other is actual. I will have them on one layout page side by side. e.g. BUDGET: Purchase Price Fix Up Costs Closing Costs Taxes ACTUAL: Purchase Price Fix Up Costs Closing Costs Taxes Any pointers would be appreciated! thank you.

Hi everyone. One of the solutions I'm working on requires a many-to-many relationship. This is my first attempt at setting this up. We have tools and part numbers. The basic relationship can be phrased as such: Each tool may produce many part numbers. Each part number may require many tools to be produced. I've been working for several days learning the principles of a many-to-many join, and I now have a structure that works. The next big challenge for me is to make an interface that is easy to use. I've attached a clone file of the work I've done so far. As far as I can tell, this is working just fine. The reason I'm posting is that I feel as th…

I am assuming that when changing data in a portal that the foreign key for the child table automatically gets updated? Example: Invoice Items (in portal): Product Number Product Description Quantity Price Extension Note: The product number is saved in the invoice items table, as is the primary key of the product which is in a products file. So if I use a popup in the portal for the product number field and show the product # and the description (calculation field) and they select another product, does the foreign key linking the invoice item to the product record change?

I have imported some data into two tables. Customers Customer's Products This is a ONE-TO-MANY relationship. The problem is, in the original application, they are related by a field called "custno". After importing, I have managed to creating new fields as follows: Customers primary key = _kp_customer_id Customer's Product primary key - _kp_custprod_id Customer's Product foreign key to customer file - _kf_customer_id After getting the new key fields populated properly and creating new links in the relationship graph, everything works perfectly; however, the links are showing the relationships are many-to-many instead of one-to-many. …

What is a simple clean way to calculate if a "record" has a double number? i.e. the number is 533, so there's 2 three's. I have a separate field titled "doubles". I want Filemaker to automate the calculate to see what the double numbers are (example: 33) so on and so forth. As you know there are 10 numbers (range from 0 to 9). Or is there a way?

Stuck in the mud again. I have set up a DB that looks up Card ID 1, Card ID 2 and Name from Customer Table and places it in the a portal from CheckIn Table. This works just fine. But, Is there a way to enter Card ID 1 or Card ID 2 in the Enter ID field and pull up the same info. See Attached. Any help would be appreciated. LookUp.zip

I am importing data into two tables. Customers Customer's Products This is a one (customers) to many (customer's products) relationship. Interesting, I created the tables in FM and have the following fields in each table: _kp_customers_id (customers table) _kf_customers_id (customer's table) and it creates a many-to-many relationship in the graph. Not sure if I can manually change that? Anyhow, my real issue is I need to update the primary key and foreign key in these tables. The old key fields were: custno (customers primary key) custno (customer product foreign key) These fields still exist in the tables, as well as the new fi…

Hi all, I have a question on coming up with proper tables and relationships for an inventory database. This will not be a typical inventory database but more of an ordering alert database. Let me explain.. All inventoried items at work have a laminated 3x5 card that is placed next to it. On the card is a barcoded label, with an unique ID number. When something is running low (a re-order point) the card will be taken to the computer and the ID number scanned into a FM field in a layout. When this happens I would like other fields on the layout to populate (ID number, Name, Vendor, Vendor Phone Number, Re-Order Level, Order amount and Last Re-Order date) Onc…

I created a check box...bascially you get a box and next to is the description is their a way to format...move...or altogether get rid of the wording thats next to the box?

I remember when we needed a dummy field with fixed auto-enter value of 1 for universal relations across tables, and I'm so happy we don't need that but... I'm feeling driven to do something similar, and hope I don't have to. Context: I'm volunteering a solution for a nonprofit preschool . Where their old flat-file solution had made mothers, fathers, doctors (and their contact info etc) into "properties" of children, I'm happily building a table of adults with join-table relations to kids -- parents of one may be emergency contacts of another, etc. Nice to have adult phone number changes reverberate through multiple roles, etc. (They're so impressed. ) Problem: …

Hi, I have 2 tables Courses table with unique Course_ID and Teachers table with 1.) a combined field with all Course_IDs separated by comma Course1_ID, Course2_ID, Course3_ID 2.) Individual fields for every Course related: Course1_ID Course2_ID Course3_ID What is the best method to find all matching Courses for any Teacher? I can compare one field but that does not give me the other Courses... I need some basic advice on how to proceed... Thank you Cheers Lars

I have setup tables for speakers and the speeches they give, can someone give me a hand and let me know what i am doing wrong. My I cant click on the portal to enter speech_id for each speech given. I am attaching a file, thanks in advance. Jay. speaker_info_Copy.zip

Hi there, I am trying to create a database at work for photo storage. Each photo will have a title. In addition to this an Excel sheet will be imported with Date, Person who took photo, Comments, and Photo ID number. What I want eventually is a layout where you can search for an ID number, or Date, or whatever and return the text data as well as the photo. Also, since there will be many photos uploaded over time it would probably make sense to make the pictures reference files, to save database room. Any input on the smoothest way to go about this? Anyone else have photo databases they have used in the past? Sorry if this is posted in the wrong s…

Hi, I wrote a script for a button that is intended to create a related record in a new table. The logic of the script is supposed to be: 1. If there is no related record in "Monitoring Data Entry", then create one and automatically populate three fields. 2. If there is a related record, go to it. It works most of the time, but for some reason, some records end up with two related records, and usually the second does not have the correct values for the fields I am trying to auto-populate (mdeNestID and mdeSpecies). I have attached the script as a jpeg. Any advice on why it does not quite work correctly is much appreciated!

Somehow, I found out I'm stuck on this: When you need to have access to a table in a secondary file, how do you control access automatically? I explain myself: If I need to see data from a table in an external file, how can I connect to it with a specific privilege set that will not allow me to change data but only view them? I don't want to use anything complicated such as External authentication. Just for desktop use.

Hi, I'm developping a database concerning sold houses. The system needs to have the following photos of the house front, rear, neighbourhood and aerial. I want to separate the photos from the main data file for maintenance purpose. Also I want to use the photo database file for other tables. I thought about the following relationship : sales (pkSaleID, pkSecondaryID, frontPic_id, rearPic_id, neighbourhoodPic_id, arealPic_id, ...) photos (pkPhotoID, fkParentID, type, photo_container, hasPhoto, ...) Where : sales::pkSecondaryID := pkSaleID & "_sales"; sales::xyzPic_id is a fix number to define the type of photo in this case 1, 2, 3, 4. …

Hey guys, I have an extremely frustration problem. Four tables: - Contacts - Temporary contacts - Seachlists They all run through a Join table People from the temp. contact table and the main contact table are added to the searchlist. They are displayed in a portal. They layout displays data from Searchlist, the portal on it displays data from Temporary contacts. It displays contacts from both tables just fine. Perfect. Same exact setup for: -Locations -Temporary locations -Searchlists (same as before And they all run through the Join table. Again, layout displaying data from searchlist, portal displaying data from temporary loc…

... but I'm not very good at this. My friend receives an Excel spreadsheet with a bunch of info on it, but the columns he cares about are: 1) the name of a band (it can be anything, and there isn't a fixed table or list of these) 2) a royalty payment amount for a song or video by that band, from a YouTube or restaurant play. These come in as separate amounts per play, from a few pennies to a few hundred dollars. Again, there may be one payment per artist, or dozens of payments. He needs a total of all the payments for each artist, so he can send them a check, minus his commission for doing the work. He has asked me for help, and I don't know the answer. I'v…

Hi folks, I have 2 tables. Table A contains essential info about patients, and Table B contains dated consultation notes. The tables are linked via the "Client ID" field. I have created a button on a layout for table A showing individual records which, when clicked, opens a table list view of the related consult notes for that client, from table B. When in this table list view, showing an individual client's list of consult notes, if I select "new record" from the menu, a new blank row appears ready for more consult notes, however is NOT automatically associated with the current client ID. How can I set this up so that when new consult notes are added …

I have an existing table (a). I added a second table (b)that has a one-to-one relationship with (a). Some of the fields in ( are calcd from fields in (a). There were already some records in (a) when I created this new record and relationship. How can I get FM to automatically create the new records in (b)for those (a) records that already exist? If I did an export of the records in (a) and then imported them back doing an update would that do it?

Ok....stupid question i'm sure.....I'm trying to create a database and I'm new to FM and databases. I'm trying to look at some of the sample databases for help. While looking at the relationships in the Invoices database I was curious how the grey relationships were created since they aren't tables. For example LineItemsByBillingID. Because I want to do something like that and not sure how it was created.

I have a database that shows employees and their managers as well as a table that shows accounts and who started the account(or who it belongs to). On the employee's table there are two portals that display all employees that employee manages as well as a portal to display all accounts that belong to them. I have figured out (with the help of this site) how to toggle the displayed data in the portal from just the info related to the current employee record or to display all of the managers and accounts that are in there immediate down-line. What I would like to do is be able to toggle the displayed data from current record data to all records downline. For e…

Ok...I apologize in advance if I'm asking stupid questions or making silly mistakes, I'm new to FM and pretty new to creating databases. I am trying to create a database the has the following info: Client name - first, last Client ID Priority level Service provider Services dates and times they attended the specified service etc Basically what I was thinking was making it like the time cards starter database with some elaboration maybe. I have the following tables: Users Providers Services UserServ - Users/services UserProv - Users/Providers Am I on the right track do you think? What I wanted to do was have one form…

I am a volunteer at a food bank that helps needy folks, and I have - perhaps foolishly - also volunteered to help with getting the food bank out of the paper index card world and set up a helpful database. I have two tables: "clients" and "visits." Both entities are related to each other on a many-to many basis. For each client there are many visits. And for each visit there are many clients. I understand that I need to resolve this into a one-to-many relationship using a "join table." Can anybody suggest an attribute of both clients and visits that may assist? Clients are visitors to the food bank and the table includes name, address, date of birth, etc. V…

This is driving me insane. Users were recently mentioning some strange behavior with one of my databases. After investigation, I found that I am unable to delete records from this database. When I try, I get the following error: "This operation cannot be performed because one or more required related tables are missing." This just started recently, and I don't know why. I've been looking through various forums (including this one, and I found many similar issues, but none quite the same), and here's what I do know: In all my relationships in this database: 0 are related to Missing Tables 0 are set up to "Delete related records in this table when records…

Hi, I have been trying to figured this out, but have no avail. I have two tables, customers and services What i like to do is to list all services name record in list view, and have a button on each record. when the user select that individual record and click on the button, have that record send to the customer's table "service" record field. customer table - service, text field services table - service_name text field I would like to be able to sumit that record from table service (service_name) field to customers table (service) field name. thank you, sorry for my English

I am a professor at the University of Michigan and I need to compare images of coins for a die study of the coinage of Eadberht, an obscure king of Northumbria in England (at modern day York) who ruled 737-758 AD. I have been told that Filemaker pro can be used for this purpose, whereby one takes a photo of a coin and then a sequence of photos of other coins can be brought up and compared to the coin in question. I am essentially dyslexic in figuring out instructions written by someone else, and can never figure out how to make new programs work. Can some kind soul tell me how to bring up one photo and keep it there and then next to it bring up a sequence of photos one by…

I have 2 tables (A & with a one-to-many relationship and the "Delete related records on this table when a record is deleted in the other table" is ON on table B. So when I delete a record on table A, all related records on table B is deleted. It's working as expected. Now, I created another instance of A & B in the Relationships window and called them A2 & B2 and there is NO relationship between them. Created 2 layouts that used the A2 & B2 table instances. Now when I delete a record on A2, the records on B2 ARE ALSO deleted. Is that suppose to happen even though there is NO Relationship between them? And I also tried putting a one-to-…

I have three separate portal rows with values like this: Apples Oranges Grapes I am trying to create a calculation that returns this: Apples,Oranges,Grapes Can someone please point me in the right direction? I am sure that there is an existing thread here that cover this but I cannot find the darn thing.

I'm sure this is probably a really easy fix and it's probably been answered here in this forum, but I'm not sure exactly what I'm looking for. What I have is two Value Lists. I want to just be able to add data from one Value List, match it to the corresponding data in the second list and populate another field. For Example, COWP relates to Ski_Winter-Park.html. COWP is in the first Value List and Ski_Winter-Park.html is in the second. Is this done as a calculation or relationship, or what? As I said, I'm sure it's an easy fix, just not sure what it is. Thanks, Ann

I have a lookup that is not working. I have a relationship that relates the datafrom field in table A to the datato field in table B. When I define the lookup table it says it will update everytime the datato field in modified. At first the two tables were related by c_Comlnk fields in both tables. Also when I run Record ReLookups it will put the if not a match default text in the datato field. How do I make the lookups work? thanks, Jim

Hi, I am quite new to this so excuse me if this is simple. I am purpose building a database.The focus is on individuals who attend different events. that relation is not a problem. The individuals are as well related to companies. If i go to the company file it collects and calculates the information about the employees visits to these events and show a summary for the company. Now the problem is that some people work at multiple companies. How do i construct that relation. Basically what i need is that the company field can store multiple values that are related to unique values. And as well how do I avoid duplicating companies with a portal use he…

i'm new to fm, but what IS a foreign key and how do you make one?? I'm sorry for the stupidity of this question!!!!

I am creating a database to track the activities of contacts, both in a calendar, and their financial records. However, I also have the difficulty that I want to be able to track the activities of couples (pairs of contacts), and then relate those activities back to the original individuals through portals. Each individual can belong to more than one couple, and of course, each couple contains two individuals, so I believe that this is a many to many relationship. I believe this means that I need an instance table between Contacts, and Couples. I have created various initial tables: Contacts - to have an ID for one of the pair Contacts 2 - second ins…

Greetings all! Quick question - I'm sure its simple. I have 2 tables - ORDERS and EMPLOYEES. when someone adds an order, I want the employee to have to pick who took the order. So, my 2 tables would look like: ORDERS Order PK Date, Time, all relevant order info EmployeeFK EMPLOYEES EmployeePK (Numeric - serial number - auto adds) First Name Last Name I have a relationship between EmployeeFK and EmployeePK set to =. All I want is a drop down box on my Order Entry Layout that when I click it, shows me First and last name. Once I pick someone, it displays both first and last names. Right now I have my drop down looking at ORDERS:…

First off, I have a relationship as follows: Clients Loans Payments The clients have a primary key that is also in the loans table (as a foreign key). The payments tables has a primary key from the loans table (as a foreign key). I am not using a portal because of the design constraints. I have a Clients Listing that allows you to display a clients details screen, and from that screen you can go to the loans listing screen. Currently, the payments screen is not finished. The problem is that when I go to the loans listing screen, ALL the loans for all clients are displayed. I thought that the relationship would filter only the loans for the …

Sorry if in wrong forum, but not sure which this would fall into. I have a "progress bar" with 8 buttons that are clickable to show a completed task in a progression method. My question is I want to create a timestamp field next to the button that when clicked enters the date and time. I appreciate everyones help for this newbie.

I am making a database for a clinical research study, in which there will be several subjects coming in for visits (about 11 visits each?) and for each visit there are several forms that are filled out, such as neuropsych forms, vitals, EKG report forms, etc. I have all of the forms that I need created already, yet some forms are not used for all of the visits. Would it be possible for me to create a "Subjects" page, in which all of the subjects are listed, with IDs, DOB's, etc, and somehow I could create a script or something that will bring me to all of the visits (V1-V11) for only one specific subject at a time? Many thanks, and sorry that this is probably…