Relationships

Inventory Database files How can I link several database files which has common items between all files. Ex. Medication Stickers - is a similar item for 15 files. If I update inventory in file 1, I need all other 14 files to reflect this same update. Thank you.

I am really beginning to wonder if I am building my relationship graph the hard way. First, I am building all my tables (I believe) in third normal form. I can see the value in doing this and have been able to get any related tables' data as needed. That is not to say that I have a fully functional interface - but I can create, modify, and delete as needed. My issue is the way I have to duplicate the TOs in order to display this related information in say - a portal. For example, if I have a record in a table like this: John Doe, Los Angeles, California …and want to show all the PEOPLE in the database on the same layout (including the persons' record I am sitt…

Hello, Can anyone help me? i'm trying for a week but don't get it. I can not display certain information in a report. See file attached. I want to create a report with productName and TotQuantity and this for a certain period (based on AppDate). I want to print a list showing the products and the quantity of it that I will need during a certain period. I think the report have to be based on the table products. This is because this table is more complex than suggested in my attachment.he's based on a file of jonathan stark 'inventory'. Each product is composed by other products and sometimes they are also compiled by another product and I want to see produ…

I'm very new to Filemaker - i've worked my way through most of what i need and tried to hire a developer to finish the rest but the brief wasn't clear and it wasn't delivered so i thought i'd try to finish it on my own. I basically have a campaign database. It does a LOT of different things, most of which is working. The one thing I can't get to work is assignings contacts to campaigns - as contacts may be assigned to multiple campaigns and vice versa. I have a layout for campaigns with a portal showing contacts, and a script trigger to let me select contacts from another layout w/ tick boxes. This is working, but only a one-to-one (i.e. i can assign contact 1 t…

Hello, Can anyone help me? i'm trying for 5 days but didtn get it. I can not display certain information in a report. See file attached. I want to create a report with productName and TotQuantity and this for a certain period (based on AppDate). I want to print a list showing the products and the quantity of it that I will need during a certain period. I think the report have to be based on the table products. This is because this table is more complex than suggested in my attachment.he's based on a file of jonathan stark 'inventory'. Each product is composed by other products and sometimes they are also compiled by another product and I want to see produ…

I'm not sure I'm even phrasing the question properly, but here's what the problem is. I'm a Realtor, and have set up several database using the 8 digit MLS number to relate the two databases. One database [TOUR] has Broker Tour Information (Tour date, time, Realtor Name, Real Estate Company Name, Comments, etc.) The other database [HOMES] has information for each property, organized by MLS number. The MLS number uniquely defines a property at any given time. A home can have several different MLS numbers, depending upon if it was on the market before, but the MLS number uniquely defines a property for any given marketing period. The TOUR database …

Hello, I have a relationship that looks like this: Direct link: http://img366.imageshack.us/img366/2411/relationship.jpg Basically what these relationships do: - When looking at a Contact (the contact table is named People) on the Contact layout, you can see what Films and Companies he is connected to. - When looking at a Company on the Company layout, you can see what Films and People the company is connected to. - When looking at a Filmon the Film layout, you can see what Companies and People the Film is connected to. Fields named "p_", "f_" and "c_" are multikey-fields. So as I wrote above, when you're on the Contact layout you can …

hello, I have a recipe file ,that is no problem. But when certain ingredients are composed by raw materials, I have a problem. I also want the opportunity to see what resources there are in a recipe. You understand? example: A cold buffet (recipe) consists of mayonnaise, tomato, ... (ingredients). And mayonnaise is made up by oil, egg, salt, mustard (raw materials). I want the ability to then see what to order for making the cold buffet. i.e. tomato, oil, egg, salt, mustard, ... i have allready search on some forums but didn't find the solution. Can someone help me?

I have a main table that contains all our sales prospects and all have different postcodes. What I want to do is create a linked database from our main databace that just certain postcodes that I enter in a list. Certain people will be allocated specific postcode areas to work with, so I want only certain postcode areas visible to certain people. For example, if I have postcodes BR1 to BR8, I would like to be able to have only BR1 to BR5 show in a particular table from James. BR6 to BR8 for Jenny.

Ever since the Leopard upgrade to 10.5.8, new cross-platform printing scripts I create on the Mac are failing on Windows clients. Existing ones continue to print OK. Page set-up is for "Any Printer," which has always been my ticket for x-plat printing. Script goes through the standard print set-up, preview mode, pause then continue to a print dialog box. But the print dialog box doesn't appear on the Win machine. Mac prints exactly according to script. Scratching head vigorously . . . Any ideas here?

I have 2 tables... Categories Category Rollups Category Rollups is a table the contains the following keys: category_rollup_pk category_fk category_parent_fk This allows many categories to be related to many categories. (I have a constrain that prevents duplicate associations via an additional field that has an auto-enter calc that concatenates the two fk and then selected the option that value must be unique - not sure if that is the best way to validate.) In any event - seems to work, however... How do I build a layout... a portal... a whatever that will display the hierarchy that clearly represents what the hierarchy is? For example, an…

Hi again! Im on my way to complete my product database and as a final thought I figured that I wanted to tagg the products, in order to easier index them when I link my database with my webpage. The general ideas is as follows. Every product is going to have a general tag; such as "pants" or "shirts", and then one or more specific taggs - for "shirts" that might be "short sleeved", "striped" or "button down collar" etc. For this I have 2 databases: "Products" and "Taggs" that i have linked together with general_tagg and specific_tagg. What i want to do is for the databas to only show the taggs specific to the general tagg! if i choose "Pants" as my …

Hello everyone I am building a report, mostly form my Products Line Items. It look like this Sub-summary - the Model of reference Sub-summary - Sector Sub-summary - Group - Price Sub-summary - Item - Qty - Price Now what I want to know, is it possible (by some relatonship or sort calculation) to have a certain group that will display only the total and not the items (when it come from the same Line Item table). For example: I need to see all the items of the Sector: Electricity & plumbing (and the 2 groups, Electricity and Plumbing). But when it comes to the material to build a floor, I just need to see the group named Floor with the total …

Hi, I have a customer table and products table. A product can be ordered jointly by 2 customers where one will be primary and the other is the secondary. Here is my issue. I am creating a table occurance TO1 by joining ralated product table and few other billing tables .One of these table has the primary flag is the table which I am using to sort the records. This TO1 has the records sorted by Primary flag. I create one more table occurance TO2 by joining TO1 which has the primary flag to be sorted and the customer table(this does not have a primary flag). When I am retrieving the data from TO2, I want the primary customer to show up first. On few layouts I just…

My db traces students activities during last year. modules students events A portal in students layout working fine showing related events... A new portal I placed in "modules" table to show related students aint wonking. Need help on this. Thanks

Hi all, I need a bit of help sketching out a very simple relationship graph that contains 6 tables. Due to redundancy, I'm building a revised solution for my friend's moving company and these are the main tables: CUSTOMERS, JOBS (ORDERS), SERVICES, INVOICES, PAYMENTS, ORDER LINE The solution should allow my friend to book a job for a customer, print an invoice and later apply a payment to that invoice once the service has been rendered. I'd appreciate seeing a few ways of achieving this as I already have a good base, but I'm still lacking on a few points. I added the ORDER LINE table to solve the many-to-many issue that I would have had if I linked JO…

I have three tables. The purpose is to get the value from the third table through lookup or calculation by setting the value in the second one. TableOne -------- ID TableTwoID Value TableTwo --------- ID Name TableThree ----------- Name Value Relationship: TableOne::TableTwoID=TableTwo::ID TableTwo::Name=TableThree::Name I set up TableOne::Value=TableThree::Value calculation. If I change the TableTwo::Name, TableOne won't get the value from TableThree. Why is this relationship not working?

Hi -- I have been looking at a couple a sample templates and I noticed that they utilize the following technique ... on the Graph they have 2 TO's pointing back to the Inventory Table ... inside the Inventory table they have a a field k_1 ... that is always a "1" and they also have a field called "Catalog_Item" this is set by a user to "1" or "". On the Graph they join (=) between these 2 fields. Which basically narrows the foundset on the Catalog Report. Is this recommended way of narrowing the foundsets? Is there a guide somewhere that shows these best practices? Thanks, Rich K.

I have a problem that I now need to revisit due to the growing size of the database... I have a job that has one customer and one company. The company can create a job and then assign a customer. Problem is this... The only way that I know of to assign a user while staying on the same layout is to use a value list to display the name while really pointing to the customerid. This works well until you have a huge list of customers... So I am looking for a more reasonable solution then to have to scroll through 2000+ names to find the correct one. What would work best is to be able to use a drop down with auto complete using the customers name... problem with doin…

Okay, here is my problem I have a database structured as follows (to simplify it radically!) Table: Matchfield Tx_Modality_1: Main: Tx_Modality_2: I want to report like this: Number of patients by Tx Modality and Year () ..........Jan.....Feb......(etc) Tx_1....N_Jan...N_Feb Tx_2....N_Jan...N_Feb ..etc TOTAL...NJTot...NFTot Alongside the ID variables in Tables Tx_1 and Tx_2 I also have: -----(Date) -----(Unstored Calc. =Year(Year_of_Admission) ) ----(Unstored Calc. =Month(Year_of_Admission) ) -----------(Number, Unstored Calc. = Main::Year_Select, Field format:Dropdown list of years) ------…

I have a small program with just two tables to be used for traking when certain events like meetings take place. A table called weeks showing the Week No and the Start and End date of that week, and an Events table which has the Title of the event and its Start and end Date. Some of the events are single day, while others are multi-day events. Can anyone help me with the relationship so that the events will show up in a portal in the right week. Some events will span 2 different weeks and need to show up in both weeks. My weeks all start on Monday Thanks John

I have 2 product databases. One in Filemaker server 9, and one MySQL Server on my web. They are connected via ODBC and a SSH tunnel. Works great. The web database has approx 3000 products, and the filemaker database 1500 products. Some of these products are the same, ie has the same part number. What I want is the following: In the web product part list, I want to show wich parts are "in stock" in my filemaker system. I use the relation partnumber=partnumber between the 2 databases. Works great. In the filemaker product part list, I want to show wich parts are "in stock" in my web product part list. I use the same relation as above, but it only works l…

Trying to create a neighborhood map. Table 1 is Residents with 6 fields, including Address and Name. This table will be populated as people arrive at the meeting, and name tags will be printed for each person. Separate record for each person, 2 people per address. I want to place each address on a map layout of the neighborhood. Then I will print the map for everyone at the end of the meeting. Tried lots of options, no luck. I tried using a Houses table, each record is an address. One problem seems to be that a layout is a record, so I get one related record for the entire map. What am I missing? What's the best approach? How could this best be done?

Well, good folk, Here is a question for you. In my mind there is one possible real use for one-to-one relationships: space saving - economic architecture. Let me give my example and see what you think: Table: CONTACT Fields: _id, name, email, tel, _id_billing_address, _id_del_add [simplified - but yes ADDRESS is to be separate table] Now, for most of my say 90k contacts this is fine. All I need, but for 1k of them I need other data: picture, biography, iban, ni, bank, sortcode, ip, salary, username, department, etc etc So purely for data economy i create a second table to hold this info in: Table: CONTACT_INFO Fields: _id_contact, 30 extr…

I have a project with over 10 tables that are linked together with one to many and many to many relationships. Two of the tables are directly connected to at least five of the other tables. This comes about because one of the tables contains financial information that can be associated with individual records in other tables. Another table represents a library of media (container fields) that can be associated with individual records in a handful of other tables. Join tables are required in some cases instead of linking directly to return-separated ids in a text field to support the type of reporting and analysis needed. Instead of creating many dedic…

This is a problem I ran across a couple of years ago but sidestepped by including all fields in one table - but that is not possible in this instance. It seems a MOST basic thing to ask FM to do - so why can't it? I want FM to display the number of records in a related child table(s) on a LO in the Parent table - but it just will not do it! To provide the simplest example: I have two related tables (Parent and Child related by serial number fields: P_SRN = C_SRN). The Child contains more records than the parent (due to duplications for re-admissions). I have a field (C_N as a summary count of C_SRN) displaying the number of records in the child table (easy peasy…

Here goes nothing... I have 3 different databases: Customers Packing Slips Control Panel Customers: has a field called Salesperson in it. This is a simple text field where we enter one of out 4 salespersons initials. Packing Slips: this is where all of our jobs data is. (i.e. how much inventory shipped out, the price of the job, etc) Packing slips and Customers are related via the "Customer" field. Control Panel: this is a new database that involves dates and sorts through the packing slips and show what shipped on a particular date, month, etc. It looks like a calendar (I used the seedcode free calendar to do this) It's all shown through po…

Still trying to understand join tables, and need help. The idea is to chop a many to many relationship in half so that the join table gives a one to many in each direction. That much I understand. You end up with a join table that has loads of one to many relationships, in each direction. But who creates the records in the join table? Do you have to do this manually, or can filemaker do it for you? Each join line has a box that allows you to allow the creation (and deletion) of records in the foreign key table (the join table in this case). But is this an automatic process, or do I have to set up a form to create each record? I'm completely…

Hi people Not sure if this is in the right forum topic, but not sure where it should be - but thought this was a better place then no where. I'm in the middle of developing a student assessment marking database. I have a "UNIT" table, with a PK name '_pk_unit_id'. Within this table is the name of the 'unit', and also the 'attempt'. There is also a mark_time (mins) included which is calculated on the unit and the attempt. Example: _pk_unit_id: UNT00001 unit: 2780 attempt: 1 mark_time: 20 _pk_unit_id: UNT00002 unit: 2780 attempt: 2 mark_time: 15 etc... At the moment I have this information being displayed in a portal, where the teac…

I have a customers table and an orders table. I want to create an occurrence of the customers table that only includes customers with no orders in the orders table. I have a customerID field in both tables. I added a relationship between the customers2 table occurrence and the orders2 table occurrence. The relationship is a "not equal to" (equal sign with a "no" line though it) relationship. So I expect a layout based on the customers2 table occurrence to include only customers with no orders, but no, it shows all customers. What am I doing wrong? Or what should I do to make this work? The purpose of the customers2 occurrence is to use in a value list. …

Hello again I need to build kits of items and I can't figure out the right way to do it. I have a product table with fields like ID, item, price etc. Now I need to build kits that would include many items from the product table. I'm not sure if I should make a new table to compile those kits. If it's the way, how can I bring it back to the product table? If there is a way to make it directly into the product table. What is the usual way to do it? Thank you

Hi, I'm clueless in finding a solution for the following common issue: I have one table with all sort of financial transactions (e.g. incoming, outgoing, from subcontractors, ...) with different statuses (draft, in progress, done). I have a second table with projects in it. I would like to include in the projects table the summary of the related financial transactions: e.g. for project X, - show the sum of all incoming transactions with status done; - show the sum of all incoming transactions with status in progress; - ... Do I need to create a seperate relationship for each sum I need or do I need to use a script for this? I appr…

I have two databases that I can easily relate to a third with a common field. This allows me to key in data in the third and have some of the data show in the other two databases, which keeps me from entering common data multiple times. However, I want to create a record in the third database and simultaneously create a record in the first or second database that includes a copy & paste of the common field, which happens to be a serial number. I can do this with a table, but prefer to keep DB 1 & 2 separate from 3 as DBB 1 & 2 are shared by different groups of people via Instant Web Publishing. How can I do this?

I have two tables that are joined together. One is called Players, the other Rosters. Rosters is the portal table. The field that joins them together is PlayerID. I have a field in Players called PlayerName. I want to add to Rosters a field also called PlayerName. I want to fill in the new PlayerName field in Rosters with the data from the Players table using the PlayerID field to validate the PlayerName data in the new Rosters field. In want to do it not only on new records I enter, but on all the existing records. How do I do this??

I'm new to the forums, but I've been fighting a battle with Filemaker over the past week, and I just can't win. Basically, I've got a database that tracks the authors and their article titles for a magazine. There are two layouts roughly corresponding to two different tables; when I enter a record into the first layout ("author") and then run a script that takes me to another layout to enter another record ("publication"), I need the first (author) record to be automatically linked to the second (publication) record. Right now, sometimes this works, sometimes it doesn't. I've uploaded a simple version of the file, hoping that one of you brilliant people can …

I'm an old-time FM user, but fairly new at this relational stuff. I think I understand basic relational setup. I'm banging my head against a problem which I'm not sure how to tackle. I'm setting up a database for a non-profit nursery school. I have separate tables for students, classes, parents, families, teachers, email addresses, mailing lists, and a few other things. And lots of join tables as necessary. I created separate tables for parents and teachers because they have some different attributes. But -- and here's where it gets complicated -- both parents and teachers have email addresses, and they may belong to the same mailing list. So, for example, the …

hey all, beginner using version 8.5 advanced. doing some work for a non-profit group with membership date problems. I currently have three date fields im working with; date first joined renewal date expiration date im doing a very basic script that looks at the date first joined and adds 365 days to it and puts that value in the expiration date field. if there is a value in the renewal date i want that to take priority and then add 365 days to that date and put that value in the expiration date field. The date first joined turns into historic data only after the first year. Im not sure if anyone has an advanced calculation that they wouldn't mind g…

I am new to related databases, though I've been using FM as a flat file databasse for ages. I am now trying to build my first related database, and I am completely stuck. I attach my efforts so far so you can see where I have got to. My database will manage a sports team. There is a list of players (about 100 in all) and a list of matches they could play (up to 12). I want to show players' availability for each match (yes or no), which player played the match, and the results. I am guessing that I need two tables: table_players table_matches I am guessing that I need the following fields: players_id players_name players_contactdetails …

I could really use some help. I recently learned how to build the TOs needed for a one-to-many recursion, as such: Table Occurrence: PRODUCT CATEGORY Attribute: product_category_pk Attribute: product_category_parent_pk Table Occurrence: PRODUCT CATEGORY is duplicated in the Rel. Graph to create... Table Occurrence: PRODUCT CATEGORY PARENT Attribute: product_category_pk which is related to... Table Occurrence: PRODUCT CATEGORY Attribute: product_category_parent_pk then... Table Occurrence: PRODUCT CATEGORY is duplicated AGAIN in the Rel. Graph to create... Table Occurrence: PRODUCT CATEGORY CHILD Attribute: product_category_parent_pk…

This question is about as basic as it gets! I'm wondering whether I have the concept of a Primary Key correct for the particular type of databases that we are using & would appreciate someone else's insight. Here is the situation: My company owns/manages a number of offshore vessels. Each of these vessels has onboard a separate database to store data/create reports that are specific to that vessel. One vessel will never need access to information for another vessel. When a new vessel is built, it is assigned a "Hull Number" (not necessarily a numerical value) that uniquely identifies that vessel. It is VERY rare that this number would change & if change…

Hi there, I hope this topic is in the correct section - apologies if it isn't! I'm pretty new to FileMaker, and have just been chucked into the deep end. I'm working on a multi-user solution, trying to implement the following... 'Table A' is list of Products. 'Table B' is a list of ProductOptions. 'Table C' has two fields ('Table A ID' and 'Table B ID') and is used to define which ProductOptions are available in each Product. Any given ProductOption can be available in multiple Products. I'm trying to create a Layout which lists every ProductOption in 'Table B', and also (using a portal) the ProductOptions currently available for a given Prod…

Hi, I need to create different serial numbers within the same file that are defined by category and that remain hard serial numbers (that is they never change). eg. Eggs1, Eggs2, Bacon1, Bacon2, etc. The solutions I found here seem to be soft, that is they change depending what the found set of records is. Could anyone give me a pointer to a solution for this? I'm sure it's simple, but my simple mind sometimes gets overwrought.

I need to have a second child record automatically added when the first child record is added. Let me explain a little more. I have an invoice table and invoice line items table in the same FMP File. The line items has a lookup to the Parts table in another FMP File. In the Parts table, there are two fields that I am working with, the "part number" and a "core part number". When a user adds a "part number" to the invoice line items (via portal), if there is a value in the "core part number" field, I would like that to be added to a new line item automatically. Any ideas on how I could do this? Thanks Shawn

Very new to Filemaker development. I am trying to set up a simple billing solution. So far I have created tables for Clients, Projects and Invoices. My problem lies with the Invoices and calculating tax. My clients fall into one of three possible tax situations. They can pay either two taxes, one tax or no tax. When I create a new client record in my Client layout, I can set that client's tax situation with a drop down value list (GST + PST, GST only, or No Tax). The client can only fall into one tax situation, and it does not change. When I create a new invoice record in my Invoice layout, I can select a client from a list, which then looks up related inf…

I am building a database for tracking inventory of garment items that get used in the fabrication of clothing. So I have the following tables built: -Raw Materials (buttons, fabric etc) -Garments (Simple table with the garment name/ID) -Garment Line Items (List of each Raw Material use in above garment) -Work Orders for Garment fabrication -W.O. Line Items (table of each garment made in the above PO) So now my dilemma is to figure out how many of buttons #1, 2, or 3 have been used up. The real issue is that various pieces of garment can use each button type in various quantities. With this structure, I can easily find the summary of each garment mad…

Hi, I have a Composer DB and a Usage DB. The Usage DB includes a Line Items Table which has fields to hold values for: - Date of Usage - Billing Quarter - Composer - Song Title In my Composer DB, I would like to have a layout with a portal to display itemized usage via the Line Items table. I was able to do this without problem. I would like to take it a step further and show a summarized report on that same layout displaying the summaries of a Composer's usage by billing quarter. So in the first portal, I have a relationship from Composer field (in Composer DB) to Composer field in (Line Items field) Does anyone have recommendations …

Hi, This question is an extension of a different query posed & answered in another forum (http://fmforums.com/forum/showpost.php?post/336661/). What I'm attempting to do is be able to assign a specific credit card that was used for payment for a service when a client has more than 1 credit card on file. The files (client_data & credit_card) are related based on client_id. When choosing the appropriate credit card ID (configured to be a drop down list showing credit card type/issuer) it shows the appropriate cards for the client however I am unable to figure out why the related fields (i.e. credit card number/security code) are not showing appropriately. This …

Hi all, I think these questions have been asked before, but I couldn't find them, so here goes. I am setting up a database for a poker event. Each week I want to be able to enter FirstName LastName MemberNumber PlayerID in a database....I can do this....then I want to be able in another database lookup a player by using any one of the following: PlayerByFirst PlayerByLast or PlayerByMemberNumber and then input Date Position (place person came) so that i can then do a report of a number of weeks and see what the total positions have come. Any Ideas guys? Thanks in advance for any help received. Milky.

I am very new to FileMaker. Is there a generally accepted way to create a new record in a child table where it maintains the integrity of the relationship.I am understand the relationship concept pretty good. I want to find a way to be in the layout of the parent table and goto the child table and have the match field data of the parent table automatically populate the match field in the child table. It seems to be a pretty straight forward process but I am not sure how to do it in FileMaker. I didn't see any discussion in the documentation. Maybe I overlooked it but I need help with it.

My client wants to click on records to indicate choices, in effect to build a random list. Then click a button to display that list. My thought was to use a global field to store his list of serial numbers separated by a carriage return, then do a Go to Related Record based on a self relationship to that global. Both the serial number and global field are text fields. This works when the global has just a single serial number, but not for a selection. Much appreciated.

Dear All, I am trying to build a database slowly in modules and I have got rather stuck with this one. In the portal for employee time and wage it repeats the value for both the time and amount. Thank you very much. Charles Forde Employee_time_and_pay.fp7.zip

Hello to all, and congratulations for this rich and friendly forum. My first experimental database is just to try things. Three tables ------ Classes (class_id; class_name) Students (Student_id; Student_name; class_id; class_name) Records (record_id; student_id; student_name; record_date; record_value) ------- Something is missing, I cant make a dropdown work anywhere...so please take a look on tha file, k? Edgard1.zip

Hello guys. I need some help. First of let me explain the database. I have created a table with countries in witch i have information about them, like name, size, population and so on. For each country i have the expenses for it. But my problem is that the expenses are divided in 7 categories and each category is divided by 3 sub categories in witch i have multiple data to add, for example in the 5ith category, in the 2 subcategory of America i have Oil and Food. How should i create my table of expenses so when i view a country i can see each category and subcategory separate so i can sum them. What i did with the income witch was just one category, i created a table, a c…

Hi All! I am trying to create a database in which our small business can keep track of our inventory at all times. One of the things that we do is create gift baskets, therefore we have large quantities of supplies with up to 5 different people taking from them. Is there a good way to have the inventory and have a way that everyone can check out the items that they take and be able to keep track of this while always having a running total of how much stuff is actually in stock so we know when to reorder? Also. Is there any way to have the "Gift Basket" as an item but when someone 'checks out' a gift basket it also takes one away from each item in the basket? …

Hi there, I have been given the task of converting a bingo daily sales recap (excel) to filemaker. I thought this would be easily done. Not so as I am not to Filemaker savvy yet. What happens is each day, the hall is to record sales for three different sessions(matinee, evening, late). During each session, there are 8 games played, each of which has a different payout structure. I have provided a screen shot of what I have already. I can get the data working properly, but its the reporting that I am having trouble with. So I think my relationships are perhaps set up wrong. I will supply a snap of the report I am trying to create next post. Any feedback would …

I am trying to run a lookup to populate a global field, without any success. Is this possible, or one of those global field peculiarities? specifically: userId_g => Get ( UserName ) userId_to_author => lookup to Contacts table, userId, show name_last. thanks

I have this problem: the relationship graph redraws every operation i do on it. When i move a table, when i add a relation... it's impossible working! It's a knowing problem? How i solve it? Please help (and sorry for my english).

I have an animal database. Each animal can have a dam and sire. I have calc fields in the main animal record that uses the dam-sire relationship from previous records so that I can create virtually unlimited ancestry for each animal. It goes like this: Animal record (unique Animal ID) contains 2 numeric fields: Dam ID Sire ID 2 relationships: Dam (matches to related Animal ID for Dam's record) Sire (matches to related Animal ID for Sire's record) Just with that, I now have calc fields that build on each one down, as follows (d=dam, s=sire) d_d = Dam::Dam d_s = Dam::Sire s_d = Sire::Dam s_s = Sire::Sire d_d_d = D…

I have set up a database that tracks inventory in my company's warehouse. When an item leaves the warehouse, the field I created to track quantity is reduced accordingly. This is handled via a script that reads input from a barcode scanner. My question is, how could I go about creating regular reports that show how many of each item has left the warehouse during the past month? Thanks for all help, suggestions, and guidance!

On a layout I have fields that are related to a STOCKS table. Suddenly I when I try and add data to the field I get"THIS ACTION CANNOT BE PERFORMED BECAUSE THIS FIELD IS NOT MODIFIABLE" It used to be modifiable. I check the behaviors and it is supposed to be entered in browse. What else can cause this? Thanks

Hi, I created a join table where records are stored via a script. I would like to stop these records from being deleted when one erases a record in the "mother" table. I tried the checkboxes in the TOG. Didn't work. Does anybody have an idea how to do this?

Filemaker Geniuses out there... I hope to pick your brains. I was wondering if I could get some feedback on how I am building a portion of this database. But I am running into a bunch of little issues with how the end-user interacts with it. I have a "Product Table" that is linked via relationship to a "Historic Information Table". The relationship is based on two fields. The "Product ID" of the product, and a global field that the user can select a "Book ID" The users like to view the product database in Table View (I know, I know... already leading to issues with this). I basically had to recreate a spreadsheet they used to use, but I had to make …

Hi, I have attached a small solution to highlight a problem that I am having. Bascially I have a calculation field (DueDateforpayment) in the table called lookup which is calculating a date based upon certain criteria. This calculation is working fine. what is'nt working is the relationship between the 2 tables. "main" to "lookup"... I am trying to view all records between the two date fields "Date_from" and "Date_to" (in the main table) where all records will show based upon the date calc field in the "lookup" table. For some reason I am getting no results showing up. I have checked everything and it seems its something to do with the calculation…

I'm having a brain fart... I'm setting up a Preferences table with company logos and other reference info, like sensitivity settings for a MetaPhone match, etc. I know I can only have ONE record and they should Not be globals (hosted multi-user database, FMP version 8.5). [color:red]My QUESTION Do I need to set up a matching relationship with every other table that's going to draw from the preferences record?

Headache...Any help would be very much appreciated. I am trying to look-up information based on a "Complex Relationship" that specifies a date range. Table A — Has event records with a declared date. Table B — Has contract records with reference numbers and rates. Each contract record has an "effective date" and a "termination date". My relationship is defined: Table B: Customer ID = Table A: Customer ID And Table B: effective date ≤ declared date And Table B: termination date ≥ declared date Problem: the "Look-Up" fields I've defined in Table A do not bring in valid data in Table B. What's broken? Also... Any other way…

Hi there In order to reduce the file size over the years, I stored buttons and other objects in global fields in my main file. Through a constant calc field, I used objects in other files. This reduces the file size. That worked fine but there is one problem. Empty files will not display objects until there is at least one record. How can I avoid this problem?

Hi, I have a nice design that precludes the use of lists and sub summaries, I think?? Problem is i have a table that records through a portal: Bldgs/Floors/Rooms/No of windows/No of lights ... and on a different tab (same layout) I want to display: Bldgs/Floors/No of windows (summarised)/No of lights (summarised) But so far I'm getting repeats of Bldgs and Floors where there are multple rooms for each floor. Any ideas how to summarise this without the repeats? I've considered updating a seperate table using just Bldgs/Floors/Windows/Lights etc. Any ideas??? Best regards, Simon

I am creating a monthly budget database in Filemaker 10 Advanced. I'm having a but of trouble translating my imagination into filemaker and my googling skills have reached their upper limit. I want the user to be able to see a percentage breakdown of all expenses by category that are listed in the line items portal. I have three tables: - Line Items, which has all incomes and expenses with the fields date, monthyear (which is the monthname and year) income_expense, category, and amount. - Categories, which has category name, category ID, income, expense, sumamount, and sumpercent - Main, has one record and is meant to be a display of all the data together…

Hi, i hope someone can help me with this, i'm trying to make a portal with a range between two timestamps my records all have a date and time field i made a selection box 'select', with '1' as the value for a [X] a calculation 'Timestamp_selected' > If(select;Timestamp ( Datum ; Start );"") resulting in a timestamp when the record is selected. now, i'd like to select two records (by putting a 1 in the 'select' field) and see all records between the two calculated timestamps in a portal. i tried this with three more calculations; > the calculationfield 'one' = 1 for a relationship called 'one': one=one (resulting in a relationship with a…

Dear Collective Wisdom (and bcooney, if you're out there!), I am once again taking a flying tackle at my health supplements db. It contains separate tables for different areas, such as Supp (the main definition of each supplement), Dose (how much to take and when), Source (where to get it), Area (what health area it supports), and so forth. There are join tables (preficed by "j" in the table name) to connect the tables as necessary. And there are various relationships defined in the relationships graph. So far so good. However, now I'm trying to add a table named Cost, which will record and further calculate the cost of each supplement. Note that each supplemen…

I am an intermediate user of FM8.5 and am in the process of updating my company's DB. We are tracking Notables in one table and Representatives in another. In each Notable, there is an agent, manager and publicist selectable by field. The relationship back to Representative is a "x" connection. I would like to add a portal to the representative's layout which shows all notables they represent but can't make it work. Either shows all Notables in list or nothing. Any advice would be much appreciated.

Hi I have been trying to learn filemaker pro 10 in my spare time for a couple of week. I have created so far one solution that was based around one table. I would like to try and learn about relationships. What I would like to build is a solution to help me learn so my spec is as follows a BOOK has many PAGES and a page has many states (not_read,read,errors, etc) So in the end I should be able to pick a book from a list and see the status of each page and its history. I havent got very farbut if someone could tell me if im going in the right direction that would be great. What confuses me is how to get data into the tables when joined. If you cre…

Hi there, I wonder if someone can help explain a relationship problem I am struggling with. I am building a TOG to show the pupil with the highest behaviour score in each school house and have a relationship setup like this: - the only sorting is on the relationship between houses and behaviour records, sorting them by score in descending order. the behaviour data table looks like this and the pupil table is like this: - but... the problem comes in the portal combining these: - as you can see, ash is correctly showing 50 - the top score for that house, but it is pulling 'bert' instead of 'stan' from the pupil…

I have a table called PRODUCTS with the following structure: [PRODUCTS] ModelNumber Title Cost SellPrice There are a lot more fields but this will suffice. I enter the information into the PRODUCTS table but when I export I have issues. I export a csv file (.mer) that has the field names. I need to be able to create a separate csv file for each of the two websites I feed to. each website has a slightly different set of field names that it requires and each website does have a little bit of unique data. My question: should I make a new table for each website and then make the field names match the required field names on the website or should I keep …

wondering if anyone has figured a way to have fmpro integrate with google calendars? it seems to me that it should be able to create new appointments... haven't found a way. help -sick of copy and pasting between layouts

Hi, all. I’m hoping someone’s able to see through this puzzle as I’m way too close to this to see the forest anymore and I may be missing something semi-obvious. Anyone have a solution or smarter way to set this up, please? (Or a reference to an existing thread I’ve missed might be helpful.) The scenario: An order comes in from a repeat customer, which has already been matched to an existing customer record. Each order has a payer and ship-to address. We would like FM to recognize if any existing records in the addresses table match either of the addresses (payer or ship-to) received with the new order. This will let FM suggest we avoid transferring any exact d…

Hi guys, I could use some help here... I need to activate/deactivate certain persons in a table so they would either show up in the overview layout I designed, or not. Right now I have a field name 'person | status' which is either 'Active / 1' or 'Not active / 0'. I could activate/deactivate a person and do a search (or have a script do a search), but that's not what I want. I want a separate layout where only active persons reside. If I use scripts to do specific searches users could bypass this giving false results... Hope I explained correctly, any help appreciated.

Hi guys I need help from someone cleverer than me. I have a medical record keeping solution in which I document amongst other things, eye pressure. There is on entry per visit but not every entry has pressure recorded. What I need to be able to do, is display in a portal, only those non empty values. i.e the empty one shouldn't show. I can only think of a complex way of doing it which involves calculation fields. Can anyone suggest a simple way of achieving this? Joseph

Greetings. I have two linked databases, one of persons and another of their titles, linked on an ID field; it's a one-to-many relationship. I need to be able to calculate whether each selected person has a variety of titles, which means checking through each element of the set of related records and setting a flag for that person. Years ago I did this in Algol with this sort of pseudo-code: For each person For each related record If the title is one of the ones needed, set a flag in the person DBF End loop of related records End loop of persons What I can't figure out is how I make the if test on just the records related to the person. …

Two tables and two TOs. TO "Users" is based on the Users table; TO "Join.target" is based to a Join table. The link between the two is this: TO Users TO Join.target GlobalField1 = Field1 AND GlobalField2 = Field2. I need to isolate (perform GTRR) on the Join table to show the single record where Users::GlobalField1 = Join::Field1 AND Users::GlobalField2 = Join::Field2. Because the Join table has no relation to the User table other than through the two global fields, I've created this script. Set Users::GlobalField1 and Users::GlobalField2 to desired values. New Window Go to L…

Hi folks - I have a medical recall system set up. I have a portal to view the various recalls, with the Recall Status as a key field, so I can filter the portal to just show recalls that are currently due, recalls overdue, etc etc. In order to be able to use the Recall Status as a key field, the calculation has to be stored - fine, since all the fields that make up that calculation are within the one table. The problem is that the status is dependent on the current date (due, overdue, etc), and because the field is stored Get(CurrentDate) part of the calculation doesn't 'refresh'. If I go into the calculation and change it to Unstored, the date and calculation r…

I'm hoping for some advice with my FM7 file. I'm in construction and am designing this file to keep track of customers as well as create quotes and invoices. Some functionality that I'd like to build in is the ability to have a list of products that I'm likely to sell and pull them into the quote. In addition, I'd like to be able to turn a quote into an invoice. I did quite a few related files in V. 5.5, but not very much in V.7 (and even that has been a while). I'm hoping someone can look at my file and get me jump started. Thanks, Bob FM_RI_Wood.zip

Hello, I am new to FM and am having problems creating multiple lines on invoices and hope that someone could help me. I want to create an invoice for a customer and be able to create multiple products lines on ONE invoice. I have a created the following table:- Customers Database Products DB Customer product DB Invoice DB Line Item DB I have created a simple version which allows one line per invoice (created without line items DB) and this works OK, its just the majority of my customers order more than one product at a time so I would be creating a lot of invoices and hope there is an easier way of creating invoice with multiple line items …

I am constructing a new file. about two weeks ago the file was called "Names". I then changed the name to "FixNation DB". Now,when I open the file, and the first time I try to select Define Database , I get a message saying: "Names.fp7" could not be opened. (Not Found). Next a selection window opens, I select "Cancel", and then everything works OK. This only happens the first time I try to open the DB Definitions window, but it is an annoyance, and I don't want to eventually turn this over to the users with this flaw. I have done a search, and there is no file named "Names.fp7" anywhere (except on my Time Machine backup disk. How do I track down…

I have spent the past 15 years in FMP 4, 5, & 6. This week I was forced into FMP 7 (because that's what the customer has). I am having a terrible time understanding Relationship Graphs. Can someone point me toward an explanation that is geared toward a FMP 7 newbie? Confused in Torrance

I have two (many actually) related tables. Each table contains orphan records and missing parent records. What is the best way to identify records that need additions to their related table(s)? Table A x y z Table B w w x x z How do I isolate w and y to add the respected records to make the sets complete?

I am developing a Filemaker product database for kitchen units. I have 50 different products each product has a variety of options including wood type, finishing and size options. Each of these options have an percentage effect on the product price. The problem is that each product has a varied combination of these options. These variables are the same across the products but not all products have all of the variables. What is the best way to structure my database so I can have a product list which on a product by product basis I can assign the variations to. I am fairly new to filemaker so apologies for my ignorance.

Hi, I'm having trouble figuring out grandparent relationships--if that's at all possible. I have 3 Tables: (1) Episode table (2) Segment table (3) Line Item for Segment table An episode can have multiple segments, and a Segment can have multiple line items. Ideally, I would have a layout based on Episode. I'd create a new Episode Record and then create a new Segment record via a Portal. I'd like to add a SECOND portal displaying and adding line items for the selected Segment. Is this possible to do one one layout? Confused. Thanks:)

I have a portal on a tab control that is used by the client to select a job to display job details on another tab control. The client has asked to have the selected job in the portal on the first tab control remain visible after the find is completed and the page is once more displayed with the appropriate job detail information on the second tab control. Filemaker's default behavior when the page reloads following the find request is to go back to the top portal row on the first tab control. I wrote a script to store the value of the portal row then go back to the portal row after the find request. The script works perfectly if the portal is not on a tab control.…

Ok I am somewhat at a loss. There is hopefully an easy solution that I have missed. I have two tables. Table 1: Products PID Table 2: Prices fPID Date Price Table 1 has a one-to-many relationship to prices. That is one product may have many different prices. I would like to create a field in the Products Table called Total Amount, which is = Amount (entered by user) and Last Price (Calculation Field). I am having problems getting the Last Price calculation. Ideally I would like to have a Vlookup function (equivalent) into the Prices table, that gives me the price as at the Maximum Date. Anyone know how to do this. To p…

I have a customer table and a bookings table linked on Cust ID I have a portal on the customer table that show bookings for that customer. If I click the booking number - using to go related record - I go to a booking for for that customer but not the correct booking as clicked, I just get a record set of all the booking for that customer Any ideas?

i use FM for my patients archive. i use two tables one for patients and one for the referring physicians. in the patients table i have pt's name and physician's name fields. what i want is that when i enter the physician name in the patient's table to be automatically entered in the the physician's name field in the physician table, so that i do not have to duplicate the work.

Forgive me if this is basic stuff but I'm pretty new to this. I have a List layout with fields from several related tables. I am able to create records in the layout without problem, however when I go to delete the record I get an error which says "The operation can not be performed because one or more related tables are missing" See file attached. Affected layout is Births:1837. Thanks for taking the time to look. Archive.zip

Are there any foreseeable problems with having 2 table occurances in the Relationships Graph? I have had to do this once, and it helped me but I was wondering...

Hi guys, I've been looking high, low, near and far; and apparently I'm no closer to realizing my solution, so here goes. I have one Table for Property records, and I have one table of user records for real estate agents. I have a layout in which Property records will be selected in find mode, and I will perform some calculation of metrics via scripts on them. Once I've calculated the metrics, I need to be able to ensure that if I'm compiling metrics for User0005 (for example) that when I go to insert this data into the Users table, that the record I'm updating is in fact the record that corresponds to User0005. I've currently got a unique, serialized, unmo…

I have a DB containing JOBS CONTACTS BUSINESSES One JOBS can have many CONTACTS One JOBS can have many BUSINESSES One CONTACT can have many JOBS One CONTACT can have many BUSINESSES One BUSINESS can have many JOBS One BUSINESS can have many CONTACTS Several people have recommended that I combine contacts with businesses however I would prefer to keep them separate if at all possible. My old DB used joins. For example the following Table: JOBS JOB_Contacts CONTACTS Along with a second table occurrence of CONTACTS labeled JOB_Contacts_Contacts This seemed like over kill but im not sure the best way of going about it.

Hey everybody, I did my best to find a solution to my problem in the existing threads, and spent hours while searching. So I hope I was not just too blind to find anything ... I am in the process of building an FM database which is basically dealing with all relevant tasks of a small business. Like this, you can put together invoices and you can register payments and link them to the respective invoices. The invoices and payments tables are linked by a join table, which not only contains invoice-ID and payment-ID but also a specified amount, so that one payment can be linked to several invoices (i. e. split up), each link being attributed a certain amount of mon…

I'm a novice user of filemaker, and had no formal training aside from playing around with it myself. That said, I am trying to make a layout where lets say has fields Monday through Friday, as well as people's name and address. What I want to do is to make a schedule, if you will- to display for instance that on Monday bob and Joe has appointments, and on Tuesday bob and Jason has appointments then on Wednesday Joe and Jason has appointments, I want to sort them out so they display from top down from Monday to Friday. That means some name and address fields are needed to be displayed more than once in the same layout, how do I go about doing that? I apolo…

This is a compilation of resources including credit with the goal of helping people better understand the Anchor-Buoy Model. I have combining key terms people have used in asking questions where this may be the answer. Circular Relationship: (I knew what I wanted to do but didn't know how to ask) If you have multiple tables with many to many relationships and/or also want to have portals showing these relationships. You can not simply relate them using a Join Table Anchor-Buoy Graph Model: Though it is not necessary to use this model throughout your graph it will provide a simple to understand method of fixing the problem above. It is rather ti…

I'm a long-time but basic user and cannot figure this out. I have a database for a workshop where a student might have several checks from various sources covering his tuition. And they could come in at different times. And one check might cover several students! I'd like to be able to enter the check info and generate a list of check #s, bank #s, and amounts to use as a bank deposit slip. I think I have all the data fields I need, but can't get things to come together correctly. I'd be happy to send the file so you could see what I'm talking about. Thanks! Steve