Relationships

I am going crazy trying to make this work!! I want to click on a photo name in a portal, and show that photo in a large container - all on a Jobs layout. I made up this quick sample file to illustrate my problem. Should be easy for one of you Gurus! Thanks in advance! :-)

Hello, I did try to search, but I could not find what I am looking for, perhaps I am giving it the wrong name. Example: Lets call the main table STUDENTS. Within this table are all students that attend the school, past and present. If the students have a housing assignment, they are considered active, if they do not they are considered inactive. I am attempting to make Table Occurance that only contains active students. I have designated a field in the student table that is a flag, if there is a value in the housing assignment, the flag reads "Active" if there is not, "Inactive". I made two auto-enter calculation fields, each one containing the word A…

I'm importing some data from a legacy database, and in the process I'm doing some field cleanup, for example changing an ID field to strip leading zeros: ReplaceFieldContents ( table::ID ; GetAsNumber(Table::ID) ) I'm also cleaning up some non-key fields. Using FMA 11v3, on a database on my local SSD. What I'm finding is that replacing data in a non-key field (one that is not used in any relationships) is about 100x faster than replacing data in a key field. The table, and the TOG, are used in many relationships, but always on the many side of the relationship. I'm confused by this behavior, because I have only a single window open. I'm also pr…

Let me start off by saying I am VERY new to Filemaker and forums. I am using Filemaker Pro 11. I know this sounds nuts but I have never posted in one before, so I hope I am going about this correctly. I work as a ticket agent for an airline so I know about how some of this stuff works (not in FM obviously) but am trying to make my own system. Here is the Situation: I have 2 tables that are part of this issue: Bookings (Passenger Name, Flight booked, Seat Assignment, etc..) Seat Maps (different types of aircraft have different seat maps) I have gotten as far as making passenger reservations, and being able to check them in for their flight (and the conn…

Hello I'm just a bit above being a total novice, I've been searching about row highlighting for a couple days now and there are so many that I am getting dizzy. I am using FMP 11 Advanced, Please tell me the most simple way I can do row highlighting in List View. I also want to have the fields in the entire row bold when it is highlighted and the current record if possible. I was able to do conditional formatting in portal rows where I have 2 occurrences of my CustomersTable self joined and CustomerID (of the first occurrence) = CustomerID (of second occurrence). That was quite easy, but I can't seem to figure it out in List View. In List View, I would lik…

Hello This is a variation of privilege sets that calculate which records a user is allowed to view and edit, but a little contradictory in nature, allow me to explain... I have CustomersTable and CurrentSalesRep I have a customers entry form, where I would also like to show the entire list of customers for everyone on one side of the form, on the other side of form, I want the actual fields for the current customer record that the user can view and edit, but only if the customer is assigned to the user. The point of having the whole list is so the user can check wether or not the customer already exists before they create a new customer, but depending on th…

Looks like I'm in over my head here - I was exploring (ok...playing around with) the power of relationships in FM9 - and I somehow lost the reference to the current table. All of my feilds show <Table missing> but the table is shown in the Manage Database menue. The fields and records are still there, I just can't get them to show on the layouts I've previously created. Any help would be GREATLY appreciated! PS - a bit of additional information - when I attempt to put a field in any of my layouts, there is not "current table" listed when the field control/setup dialog comes up.

I made a terrible mistake by messing with my real database before I made a copy. I now have a error showing up in every field and can not access my data. I have already tried a file recovery, didn't work. If somebody can help me undo my mistake so I can access my data I would be greatful. Claudinne [email protected]

Dear all, I am trying to create a mirror of a database. So basically a secondary file that only needs to display 3 fields from the primary file. This secondary file will be shared through IWP so users can see the content of the primary file. What is the best way to do this? Right now, i have added the table (table A) from the primary file to the relationships tab in the secondary file. I create a new layout, choose to display fields from table A, and the data shows up fine. The problem is that under IWP, it shows no records. I have tried many different things to no avail. All access rights, sharing settings etc are correct, so i expect i am doi…

Hello FMP idols, Am a novice to FMP What is the best way I should keep track of attendance for my customers... I have a customersTable, in a form layout, I want to have a field with checkbox set, in this checkbox set, I want to be listed all the events I've held, and all the classes I've held. So basically, when viewing each customer in the form, I will be able to check off every event and class that the customer has attended. After this I would like to be able to do reports where I can list all customers that have attended a certain event, or class, etc. I notice that if I do a custom ValueList with the names of events and classes, that I am able to c…

Hello All, In my database I have a table which shows details of parts. Multiple suppliers can supply the same part. So I have a portal on my layout which is a self Join onto the partNumber field to display the names of the Other suppliers. So if I am on record of Part# :abcd from supplier XYZ , and this part has also been supplied by suppliers BAN and CSF, then the portal displays the other suppliers and their prices. However it ALSO displays the current supplier - i.e. in the portal I also get supplier XYZ. I would like to omit that from displaying in the portal. So currently I'm getting this - when I am on record for part# abcd from supplier XYZ: …

Hello compassionate souls, I have a CustomersTable with fields CustomerName, CreatedBy, CurrentSalesRep I have also created a "Sales" Privilege set where the user has access only to records where the customer is either CreatedBy the user with no CurrentSalesRep, or else CurrentSalesRep is assigned to the user (I eventually want to make it so that the user can see (view only) customers that they created wether or not CurrentSalesRep is assigned to the user or another user, but that is another issue...) I am trying to make a form on the CustomersTable where once the user logs in with the Privilege Set "Sales"... on the left hand side of the form I want a view…

I have a database that works without issue but I need to export content to an ancient legacy system. I need to completely populate a set of fields with placeholder info. (i.e.0, \0 null) that people can overwrite with relevant content to any given part of the field set before sending the export file to the dinosaur. All the fields must be populated to begin with but I don't know the best way to do this. Any help on this would be terrific.

I've spent a while playing with Ray Cologan's Ultralog demo file. The file contains the building blocks for an audit log. It all seems nice and cosy when you're working on a flat file solution: a) autocalc field records changes to records in the same table and b] Looping script on FMServer can be used to push changes to an archive table. I'm trying to list the features I need to build into my solution and I'm feeling a bit uneasy about the extent of work involved. I've decided that the best way to show the modification log is to use a portal to display the archive records from the archive table. a) ARCHIVE PORTAL INCLUDES RELATED INFO: In the invoice record I'll u…

I'm building what I have been told is a complex course management system. I originally built the whole thing in Apple Numbers about 2 years ago and managed to cut my workload and that of others significantly by doing so. I am rebuilding it in FileMaker because it got seriously out of control, plus there are a lot of things that are obviously difficult to achieve in even a Numbers spreadsheet. I am also planning for this to eventually be accessible on iPhone. I'm completely new to databases and Filemaker, but I've picked up enough to get me started, but now I could use some help, not sure if this is the right forum for the problem I'm having, so apologies for tha…

Hello, I have a situation where the expected result of a relationship is odd/wrong post insertion of an additional table into the solution; the data returned now is for a different individual. The insertion of a join table between 2 tables IMO should have no bearing on the returned data if my understanding is correct (and it may not be). Below was the previous working relationship info between 3 tables: clients-> jobs-> cards clients is related to jobs by client ID, jobs is related to cards by card ID. Card data could be viewed in either the client or jobs tables via a portal. New table info: clients->join-> jobs-> cards …

Hello friends, I have a customerTable with fields customerID and currentSalesRep. I have another customerNotesTable that acts as a line items table for keeping track of notes related to the customerID. I have match fields being customerID. In a form layout on the customerTable I have a portal to the customerNotesTable line items with fields NoteField, CreatedBy, & DateCreated I am trying to find a way to have a new note automatically created (new record on the customerNotesTable) when modification is done to the customer record in any field. In this example, if I change the currentSalesRep for a customer in the customerTable from "John Deer" to "Jane Doe," I…

Hello friends, how do I have a separator mark such as ":", or "-" in between merge fields, only if the merge field contains a value So: CustomerName John, StoreName Acme would look like John - Acme But CustomerName Jane, with no store name would plainly look like Jane TIA

Hello, can someone give me advice on how to set this up, I don't even know if I have the tables and relationships correctly thought out: I have a CustomersTable with the field "SourcedFrom" where I want to keep track of how a customer may have come to know about my business, this could be from magazine ads, flyers, tv commercials, word of mouth, etc. Eventually I want to develop reports on how many customers a certain advertisement or marketing venue is generating, and then even further, how many of those customers eventually actually make an order and how much… But first things first, In another table, I have an Ads_n_MarketingTable that the records will h…

Hi all, I am working on a participant database to store which participants are involved in our different programs. There are several different participant types, and while most of the information we need is the same across types, there are several pieces of information that are only relevant if a participant is a certain type. To avoid having a ton of fields in my People table that will be empty in some portion of the records, I have an AdditionalInfo table linked by ParticipantID to the People table. In the AdditionalInfo table, I also have Property and Value fields. I have set up a layout (based on the People table) where when you choose a particular participan…

In my organization we have 15 buildings within a county that we have fixed distances between and offer mileage reimbersment. Our Technicians use a FM DB to track inventory and work orders on the inventory. Within this DB is a built in Scheduling system to assign the Technicians to a building for all or part of the day in instantances where the Technician travels between buildings they are entitled to mileage reimbersment. The Director of Technology now wants the FM DB to track the mileage for the Technicians since the information is already in the DB and produce a report for the Technician to sign off on. Here is what I have to use: Tbl_Appointments Appoint…

Hello everyone, I have 2 related tables: 1 with the date and ID of the inventory (Inventory check) and another with all the info for each product (line inventory check). In the layout of the table "Inventory check" I set up a portal from "line inventory check" with a field (code_products) where the user can select a code from a drop down value list with all the products codes and another field to add the number of items in inventory. I also made an autoenter calculation field in "line inventory check" (code_products & IDinventory) with a validation as "Unique Value", In this way the same products will not be committed twice in the same inventory day. …

I am using FM 11 Adv I have this relationship: I have 2 layouts: This layout collects detailed visitor information. It works. I have a Meetings Layout based On MEETINGS:: that collects the names of Members and Visitors. Visitor names are collected via a valuelist based on Visitors::Name_Last. This works. (see .jpg below) The problem is: "If I put any field (Like VISITORS::TITLE) from VISITORS on the VISITORMEETING JOIN portal, the field will not reflect the value that it has in ViSITORS. It seems like this should work. But it doesn't...? Coincidentally, GTRR from the "VisitorMeeting Join::Name" portal record to Visitors also d…

Hi Sir I have created two record for my stock use record, one call shift stock use input and one call shift stock balance. This is to monitor the stock use in each shift and what is the balance after each shift. Our shift hours for Day is 0800 to 2000 and Night is 2000 to 0800. When the shift start they will open a new shift stock balance record and the record will capture the last shift stock balance to the new shift stock balance open balance. My problem here is that both shift run in the same 24 hours and when the night shift operator input the stock use before 0000 mid night the date go into the day shift shift stock balance record. When the operator on …

Hello, First, Excuse me if i can’t explain my problem clearly because I m not English, But really ,I need your help so I have think to participate to this forum! I have a database for “school” with 3 tables, 1_Students 2_Classes 3_Inscriptions It works very well; But now, I need to evolutes it on adding a new table for insert “marks of students”; This table is naming “notes” My problem, I want to visualize in this new table all names of students changing with them class in an extern table (to add their marks), but I couldn’t establish the necessary links, I searched a lot but it was impossible, Can someone help me to resolute this problem pl…

I am using FM 11ADV I have been wrestling with this perplexing problem. (Please see relationship diagram below) The layout is based on Meetings (Yellow) On the Meetings layout, there are 2 tabs. The First Tab is named Members and has a portal based on MemberMeeting Join and it works. There is also a Members 2 valuelist based on Members 2. MemberMeeting Join::MemberName uses that value list. THIS WORKS. But, the identical setup (in gray), does NOT work. I get a single Visitor Name when I use the value list just like the one I use with Members 2. huh? Is my FM datbase corrupted? Or, (more likely) "What am I missing???" …

Hello there! First off, I have experience using MS Access, MySQL, and PHP front ends for database design and presentation, but I'm a newbie to FileMaker and am having some trouble picking up on it. I'm contracting for a manufacturing company and setting up an FM database to be used primarily with FM Go on an iPad for on-site equipment inspections. I'll apologize right off the bat for being long-winded, but this project requires quite a bit of exposition. The company I'm working for has multiple locations, each location has multiple departments, each department has multiple areas, each area has multiple pieces of equipment, and each piece of equipment has multi…

Hello, I am pretty much a newbie to FMP, using FMP 11 I kind of figured out the go to related record script... however, my situation is : I have 3 fields that contain account name in my CustomersTable; CreatedBy (autoenter), CurrentSalesRep (which I assign an account name to a customer), and CurrentUser (a calc field with Get (accountname)). Right now I have a self join on the relationships graph where CurrentUser in CustomerTable is related to CurrentSalesRep in CustomersTable2 (same table but self joined). When I run the go to related record script, it works fine, and shows shows me all customers assigned with the currently logged in user. However, w…

HI please see attached file. I have 3 tables 1 that stores users, 1 that stores books, and 1 that stores books read. What i am trying to achieve In the Users layout i want to show a portal showing all available books from the books table, but i want to highlight using conditonal formatting all the books read by the user. Please help as i am seriously stuck on this Thanks mark test.fp7.zip

I have a contact/organizations database, and I want to list the contact's co-workers. More specifically, on the contact page, I want to list all the contacts who are members of the organization of which the current contact is a member. But I don't want to list the current contact in the list, just the coworkers. I have no problem showing all members of the organization, but I don't want to list the current contact. My relationship involves 3 tables Contacts, Org_Members, and Organizations primary IDs in the Contacts and Orgs tables equal the secondary IDs the Org_members table. Is a self-join somehow the answer?

Hi Friends! I have this questions!! I have a layout with a portal, with 12 rows. But the portal shows between 1 or 12 rows. If there is only 1 row, the portal shows the row with data, and 11 empty rows. I only want to show the rows with data. How can i do it? Many thanks!!!

Hi everyone, I've been developing a Members database and there are a lot of 1:many relationships. Most of the many tables show in a portal on the MEMBERS layout; this works great... Until I added a MEETINGS table. Trouble #1 The Meetings layout is based on Members. The portal is based on Meetings I tried to filter the portal with Meetings::date=Meetings::gDate (gDate is global) But, this was just 'wiggy' and unpredictable in it's outcome. I tried (from reading the other posts), to put g_Date in Members and adding a relationship to Meetings::Date but that didn't work either. This begs the question "What good is portal sorting if I can't set M…

Hi all, Part of a solution I'm working on needs a section to assign students to various projects. Each projects is made up of 1-20 tasks from a list of about 100 tasks. I have set up the following tables: Students_Table Join_Students_Projects_Table Projects_Table Join_Taskes_Projects_Table Tasks_Table I want the users to create new "Project" records by choosing from a list of predefined templets, and I have thought of three ways to accomplish that task: 1) Just put a template flag field in the "Projects" table and use filtered portals for the list of templates. 2) Create a "Templates" table and store the TaskIDs in repeating fiel…

I will try to explain this in language that we both understand. I do not get the FM lingo quite yet- I have a very simple project management system I am tweaking. I have one layout which is Projects, and another that has Tasks. I have figured out how to link them, so that a new task posted in a Project is showing up on the Task layout. So far so good. But when I add a task on the Task layout (and designate a project), I want it to go under the correct Project. It is instead creating a new Project, even if the name is the same (ie it creates a new project by the same name instead of adding the task to the existing project with that name). I know there i…

HI Is there any way when you bringing in records you can bring in the related field names for a delivery note the item will change in the order they are displayed, so i want to able to bring in the records and the field name i delivery note THX

I have just started using FileMaker. I am stumped in a many to many situation as depicted below. The payment table has a primary key for each payment. The session table has a primary key for each session. A payment can go to multiple sessions. A session can receive payments from multiple payments. I actually also have a table that is what the join table ( session payment table) should look like meaning it has the six records listed below with a foreign key linking it to both parent tables. I basically imported all historical data from Bento via Numbers. This historical data has been entered into FileMaker. The session table has been populated and the payment table has …

Hello! I have configured a portal to show registers from "Table1". But the user can modify the registers in the portal, and i would like to set the registers as "non modifiable", so the user can only see but no modify the registers. How can I do it? thanks!!

Basicly I'm computerising a school system. I've got Student and Subject table, and also a StudentSubject table that links the two so many students can take many subjects. I've got a portal showing the records linked to it through StudentID to SubjectID The portal only shows records when i manually input data into my StudentSubjects table Is there any way i can create a new record with the same StudentID in the StudentSubject Table from my Student Form to prevent me having to go to the StudentSubject table and re-entering the StudentID for every subject each Student is taking? Sorry if that made no sense, but please help!

Hi! This feels like it will be tricky to describe concisely, but I'll do my best. Hopefully the picture helps. To summarize in one line: Summary fields in my crosstab report work for most records, but in part of the report they are blank for no apparent reason. BUT, other fields that depend on these "blank" fields for their totals have correct amounts in them! The details, in a nutshell: My database is a list of donors to a charity, along with each donor's individual donations (in a related table of course). I've created a crosstab report using the technique described in Filemaker.com's Reports tutorial. It "mostly" works correctly but I'm getting som…

I’m a newb trying to understand FM relational design. My question is, if 2 tables are properly defined with a relationship and a new recored is created in the parent table, is a new [related] record automatically created in the child table? If not, what are the basic scripting elements to create the related record and programmatically populate fields with data? No portals are being utilized for this project.

OK. So I am very new to FM. I have been using Excel for many years doing DB-like things. But I figured it was time to move on and start using an actual DB for my projects. I am confused and I have been looking for something that makes sense online to answer my question. But I am still stuck. Here is my problem and I hope someone can help me with a solution. I am building a DB that will track events that my church holds every three months. We call them Encounters - kinda like a retreat. Anyway, we have participants that attend and volunteers that, well, volunteer. A person can be a participant only one time but once they participate, many want to volunteer to serve in…

Hi relevantly new to filemaker, be kind ! I got 8 different databases trying to link them into one from really old versions of FILEMAKER there one relationship field i cannot seem to get it right could someone please help i ve attached a sample what i have is a dongle and delivery note product code tables , we import into the dongles table via excel which is no problem Product code where code is given and in rip options are select fields from the dongle table IN delivery note table by selecting Product code selects the products , by selecting Dongle SN it will populate the the field name and relevant numbers from the dongle tables. Ive manage to …

Hello, I need some assistance overhauling an existing database. I've inherited a database with one table and I am wanting to know the best way to split it up to do what I want. The database is for a summer music camp. Here is the current setup: Table 1 - Faculty Member Information Name 8:25 Class Name 8:25 Class Location 9:30 Rehearsal/Sectional Name 9:30 Rehearsal/Sectional Location 11:00 Rehearsal/Sectional Name 11:00 Rehearsal/Sectional Location 1:00 Class Name 1:00 Class Location etc. Lots of other information not really relevant (fax, cell number, salary, email...) What I want to do is to be able to create several layouts to …

Hello friends!! I have this question. I have a database, with three tables (presentations). I want to share this database in my local network, so the users can access to the database. But there is one table, "table 1", which is very important, and only a superuser can access it. The rest of the users only can access to the other two tables, and the superuser can access to all tables of course. Can I do this with FileMaker pro Advanced 11?

i would like to compare visually different record (same table) into the same layout, example: computer1 computer2 computer3 Processor Name Intel Core Intel Core Intel Core i5-480M Processor Speed (MHz) 2130 2130 2660 Processor Cache Memory (KB) 3072 3072 3072 Front Side Bus (Mhz) 1066 1066 --- System Memory Standard RAM (MB) 4096 3072 4096 Upgradeable RAM (MB) 8192 8192 --- Hard Disk Drive Type Inbuilt Hard Disk Drive Capacity (GB) 500 320 500 Speed (RPM) --- --- 5400 Optical Drive --- --- --- Display Features Screen S…

Hello Friends! I have this problem. I don't know how to update a formula, in a presentation 1, which depends on a related field. this is the formule: cost= total_cost::total - total_cost::total * 0,5 So, when I edit the "total" in presentation "total_cost", the value "cost" in presentation 1 isn't the actual value. Please help!! thank you very much!!

Anyone could help me on this? I have two tables, payments and people. They are related through PeopleID. People must pay a monthly fee. I have a field (PaymentSituation) in Peoples table that through a calc tells me if a person owes money or not. What I need to accomplish is to know the right data on a giving date. For example: if a person didn’t pay on January but now has fully paid, the paymentsituation tells me that doesn’t owe anything, even if I take a look to January. I don't know if I can't make myself understood How should I relate the tables to show the right data on any giving date? Thank you very much.

I have an Client | Estimate | Job | Invoice solution that I'm redesigning from scratch. I now need to incorporate the functionality to record payments. Ordinarily each payment is for one invoice paid in full. But when I receive an advance or partial payment, or if the amount of payment for some reason is not identical to the invoice amount things get more complicated and I want the flexibility to handle this gracefully. The Invoice structure is simply an Invoice table with a child for the line items. I don't really want to separate payments from the individual invoices––iow, I want to be able to credit a payment to a specific invoice––but I also want to have the ability…

I have a Person table and a Payroll table, and they are related by a numeric ID number. The Payroll table has entries only when a paycheck is issued (once per month, on the 1st). I need to calculate, for each person, the # of months since the last paycheck. This is in a very large database (nearly 100,000 people with several million payroll records) so performance is critical. Question: what's the most efficient way to do this calculation? * Using an un-sorted relationship based on Person::ID = Payroll::ID, I could use Max(Payroll to Person::Date) However, logically this requires FM to access every related record to figure out which is the …

I got a weird thing here... I need some clarification here. I have a kind of complicated system I am doing for a client and it is live but now they want a lot of different layouts for each position of their workers. That sounds fine but each different layer will show data from other tables from different keys. Already the base occurrence is getting a lot strings flowing from it. I want to move to different TOG for organizing purposes but a lot of scripts and portals rely on that relationship from the main occurrence. How would I create a new TOG without having to duplicate all those scripts and goTo items?

Hi everyone, I am stuck with a lookup to automatically populate some fields. Tables are 1. Jobs and 2. Sites Join is via Jobs::_id_Sites = Sites::_id_Sites Other fields are: Jobs::SiteAddress (Drop down list from Sites::Address) Jobs::SiteContact Jobs::SitePhone Sites::Address Sites::Contact Sites::Phone I want the fields Jobs::SiteContact and Jobs::SitePhone to be auto populated from Sites::Contact and Sites::Phone respectively when the user chooses a Site address from the drop down list. I have tried so many times and cannot get it to work. Any help would be greatly appreciated. Milton.

Hi everyone, I'm a real newb when it comes to using Filemaker Pro Advanced 8 and I have a little problem. I want to show a dropdown menu on some lay-outs which shows records from other tables. The problem is that when I'm trying to use value lists I can only use 1 or 2 tables to show the records from. So my problem is that I'm trying to create a field with a dropdown menu that shows records of several different tables. I have one table called "inbetween" with all the nessecary values for this. Those values are: User Name License Number Computer Name Serial Number I have a lot of other tables who need to have a relationship to this one. …

I'm very new to relational databases and have found a lot of information here that's similar to this topic, but that doesn't quite solve my problem, and I'd greatly appreciate some help. I'm developing a database and having difficulty with three tables in particular; people, projects, and outreach. (Please see the attached relationship diagram for the structure.) Each layout for each table has a portal that displays related entries, so for example from the people layout you can see what outreach and projects that person has done. The problem is that I am unable to create projects and related outreach that have different authors(people). I understand why this is ha…

Hi I have a very simple problem but am wracking my brains trying to solve. I have a table of contacts and a table of invoices linked with a join table At the moment if you alter some personal details on the contacts ie the surname it will change on the related invoices Basically I want to prevent this happening Can anyone kindly point me in the right direction Many thanks

I want to keep the track of meetings. I have, meetings, points of the meeting and participants tables. I’ve done a relationship between meetings table and participants one through MeetingID, so I can say who participates in the meeting. But I don’t know how to go further. What I want to do is: set a meeting, set the different point to work on, and when the meeting comes set the people that participate. Then I need the different points to be individually voted. I’ll try to explain with an example: Meeting (day 02/14/2011) Point 1: salaries increase, Point 2: expenses, Point 3: holidays... When the meeting takes place, I set the participants: …

I have a quick question regarding printing value lists. Instead of trying to explain what I am trying to do (which only confuses me and everyone else), I'm inserting a screenshot below. The image is of a hotel rooming list which is sent to the hotel when the reservations are made. Upon arrival, the hotel assigns room numbers to each guest. Using a value list I have the guest names added but I need to add a line for the hotel clerk to write the room number on. For aesthetic purposes I would like to keep all the guest names left aligned and would like to have all the lines the same length and right aligned. Is there a function (LeftWord; MiddleWord; RightWord; e…

I have two tables: clients and employees Employees have designated titles, Account Executive (AE), Account Director (AD) and so on. Clients are assigned up to two employees per employee title. So CLIENT A has two slots for AE, two for AD and so on. Clients also have hours assigned (per month) for each employee and hours worked for each employee. So for CLIENT A, JOHN SMITH (an AE) is assigned 20 hours per month and has so far worked 16. MARY JOHNSON (an AD) is assigned 38 hours and has worked 21. And so on. Make sense so far? God, I hope so. Within the employees table, I have an ACCOUNTS tab that contains a portal listing every client to…

I have the relationship above. Proficiency POPUP and Proficiency POPUP 2 exist to provide means to have a 'dynamic' value list. HERE IS A SHORT EXAMPLE: The two fields above are 'degree' and 'description'. There are 3 degrees but there are 'many' of them. Each degree 'row' has it's own 'description'. I want the user to click the degree. Select from the dropdown and then according to what degree is selcted, have a limited selection in the next dropdown field. This seems to work with this unusual exception. When I click the Degree popup, I get this: 1 1 2 3 It should be: 1 2 3 If I click the 2nd '1', I get a single pop…

Hi Everyone- Im new to the forum and to Filemaker. I've read many posts on here which have really helped me but Im stuck on this one. Im hoping I can get some help from you all, please forgive me for I am just learning this program. This is what I want to do: I want create a material requisition form for work. It really doesnt need to be that complicated. I have put in the order number, current date, due date, and customer name which will all be entered each time I need the form since we dont manage our customers on FM. Thats all fine but I run into a problem trying to create line items for the different materials I need for that particular order. I…

I have a database where I'm going to be importing images for students. Sometimes the students will have one image, sometimes they will have two or three. I used a portal before to show multiple images related to the particular student, but that was when the information was manually entered. I would copy paste the student id to multiple image records to relate them (I know, scripting). I now need to import the images, haves some sort of custom dialog where the user is prompted to enter the amount of images per student (it will always be the same for all) and upon import assign an incrementing id, but based on the number inputed duplicate the id how ever many times. Th…

Hi there ! I have 2 tables in the same file that are almost identical. Table A has a field with a serial key in the 0001 format. The Table B has also a serial key but in the format FIT 0001. In another table, I want to be able to lookup from both tables. Is it possible ?

I'm working on a solution right now that is drawing some pretty complex relationships between TO's. It's a little hard to explain in a short post but put simply a parent record may have as many as 10-20 child records that creates a validation. What I've discovered is that in the course of a work week there may be as many as 100-150 parent records created. Of course, this now means that every week there will be 1,000-3,000 of these validations created as part of the parent record. My question is, how much is too much? After one year you're looking at between 52,000-156,000 records. Plus, that's only if volume remains static (which it's not projected to). On the …

Hi: I have a database tracking information about products our architecture firm uses in projects. Right now I have a 'Products' table and a 'Manufacturers' table. I would like users to be able to select a manufacturer via a drop-down list from the products layout, with a button to 'Add a New Manufacturer' that would take users to the manufacturer layout. I created a join table (Product_Manufacturers) to avoid creation of a new records in the Manufacturers table every time someone selected a manufacturer from the products layout. This works fine, except that on the manufacturer layout I can't pull up a list of products carried by each manufacturer. In the join ta…

:( It's official... I suck at relationships. I admit it. But, I'm not too proud to ask for help Re the attached - Because you can't have a relationship on a calculation, I need to come up with a better solution. Basically the story goes that only those works available (i.e not sold) will be listed when selecting from the line items drop down list. I tried to re-create a technique with a major fail There are several tables involved Artworks Invoices invoice line items Artworks has a relationship that identifies if an item is sold, based upon a relationship of the SerialID to invoice line items. Hope that all makes sense :S TIA ShowS…

I have built a project management database. Projects are parent to Assignments. Assignments carry the service/product and billing info. So the question is... How do you handle/create from "Project" to "Invoice"? Because in essence a Project is like an Invoice and Assignments are like itemized Charges. A mirrored structure. I know I can not simply label Project status from "Bid" to "Invoice" because a Project may stay active for many months and require several billings during this time. So what I have done is... In a separate invoice table I'm creating a record that carries/relates to Assignment(s). Via this relationship Assignment is mark as "invoiced." So a Pr…

(there is every possibility that I have posted more than once and if so it is operator errorâ�¦) I am setting up a solution for my wife's yoga classes. The solution will run on her computer and she will access it via IWP on her iPad in the same building. A sweet setup, I hope, and no intent to make it complicated. Among other things, I want to use it for attendance. Each class, in my thinking, is a record. She would have a check-box presentation of all her current students displayed in each class record. She would check off each student as present, or not, and, ignoring font and other presentation issues, would look something like the attached 'attendance.jpg': …

I have an <index missing> message on a field that I'm trying to relate to in another table. I'm only trying to match the Assessors title with Assessors name on a form,this information is stored in a table called Assessors. The relationship for 'Assessor' goes from the Assessor table to the Assessments Comments table via the Rules Assessment Exam table. This should still work shouldn't it?

hello, a fresh view by others might solve this in a minute. table pict has an indexed field called xmp_index full of values containing broken down xmp descriptions of pictures in the form of 2008-01 Di Europe Germany Hamburg Leipziger Str. 39 Anna Paul - (every picture ends with a minus to satisfy empty search_part fields, see below) table search (i like to save sets of searches) holds several fields which should filter those values with an AND-condition search_part1 may contain "2008-01" search_part2 may contain "Hamburg" search_part3 may contain "-" search_part4 may contain "-" this would yield all pictures that where shot in h…

I am using FM 11 Adv in OSX I am stuck. Here is the layout. Tables: See Diagram Below Narrative: I have an organization of 100 members. Each candidate for the organization needs sponsorship from 3 members. And of course, a member may sponsor more than 1 Candidate. (Sounds like a many to many relationship to me?) On the Candidate layout (based on the candidates table) I am trying to put 3 dropdown boxes in which each will show all the members for the users selection. I have tried: Putting the KF_Candidates in the Members table to get a 1:many relationship. Didn't work. I have tried: Creating a many to many relationship with a M…

Hi all, I have already created a Contacts table, and a separate Events table. We have charity events throughout the year that are dependant on volunteers helping to make it happen. Basically, I have fields in the Events table such as Event_Name, Event_Date, Event_Time_Start, Volunteers_Required etc etc.. What I want to do, is if a venue (whose details are in the Contacts) has agreed to let us have an event, we would like to link the event to that contact - if it was just one event per contact, it wouldn't be a problem, but because one contact can have more than one event, this is causing me a problem and I don't know the best way to go about it as I ne…

Sorry for the English language I'm planning interface table in the database about the People and companies who need a filter to search var.nasıl can help. s also where you will install the portal to make entries. Thank you in advance. Cont.zip

Hi all, I have created a database so that we can record all the relevant details for charity donors etc, but I am stuck on how I can create a history of correspondence, so that if that person calls, e-mails or speaks to me, I can look at their corresponding history. Basically, I need to have the following: • Date (of correspondence) • Method of Correspondence • Brief Notes This would need to be linked to the main database as we'd like to keep the notes and their fields in a different table. The bottom line is, if Mr 'X' phones up, we can put that contact up in the main database, and look at their notes which will be under a tab called "Corresp…

Hi, I've been doing FM for a few years, but never used calculated multikeys since they don't exist in SQL database (well if they do never used them). By multikey I understand a calculated list from different field. What is the theory between these kind of relationships? Thank you

I'm not very expert (newbie-ish)... and would appreciate any advice. I've created two very simple databases to help me manage my student placements. db - A contains student data including student placement details (company name / address etc) db - B contains placement data (company name / address etc) The problem I have right now is that I've designed it so that when completing the placement details from the student db it filters directly in to the placement db creating a new record. (I think I've done this through a relationship - but I'd be lying if I said I knew that was the correct terminology). I'm happy with how this works. However, on occassi…

Is it possible to display a Related Record in a different tab? Yes, I can set it up to view in a different layout Or I can view it on the Current Layout But I can't seem to figure out the work around on how to select the record in a portal on Tab A and have it appear in a detail layout located on Tab B. Is this an easy fix? Thanks in advance. The folks on this forum are fantastic and generous with their time.

Hi everyone, This has really stumped me. I have been thinking for a couple of weeks on how to solve this; and can't crack it. Essentially I am trying to setup a database for a customer (FMP11) for furniture. Pretty simple. One of the things they would like to have, is a related products section on the layout for each product. This would be a portal displaying all the furniture that is similar to the current item. The user would establish this relationship by adding the ID to the portal; and creating another 'similarity' relationship between the current product, and its similar brother/sister. This is where it gets interesting. The related product, is not …

Problem: I would like to create/compile a table with Text data from other table.. firt table: recipeT Type Qty recipe1 food1 100 recipe1 food2 200 recipe1 food3 300 recipe1 food4 400 recipeT Type Qty recipe2 food1 100 recipe2 food2 200 in the new Table: firt table: Type Qty food1 100 food2 200 food3 300 food4 400 food1 100 ]-from second recipe food2 200 ] and after, aggregate te result food1 200 food2 400 food3 300 food4 400 How can do that?, I have the first table that work well...but I don't know how can create the new table.. help

i have a sales and inventory solution which works very well, what i want is to track my stock list changes by day and get a sock valuation report per day or per month. is there any one who can help stocklist.pdf

On one layout set to display a master record/table, I want to show all the records from a secondary/child table in a portal. (If it matters, the child table is also related to the master in a direct ID to ID relationship too.) The best option I've come up with so far is having a global field = 1 in the child table and the master having a stored calc or actual numeric field for each record always set = 1, then relating the two tables on these fields which then shows every record. Obviously they can't both be global fields or the relationship doesn't work. Is this the best method, or is there a simpler one? SOLVED just as I posted! My Solution: I've just…

Hello everyone, I wasnt actually too sure where to make my thread, but I figured in the relationships section would be best as this is the topic en which my question pertains! Alrighty, so here is a little introduction first... At the moment I am currently working for my father updating his companies website with new product and what is currently available for that product. I want to be able to keep a specific inventory of the product and what items are still available (an inventory system). The main issue is that my dads stores specialize in the shoe business and as I have found out I have no idea where to start in regards to how to keep track of a shoe invento…

This one's going to take a bit to explain - I hope someone has gone down a similiar path, and is willing to tackle this one! My DB tracks apartment rental licenses and inspections. Each month, I need to send an invoice to owners or managers who have units due for inspection the next month. I have a summary letter than prints just fine, sorted by owner, listing the units that are due. The next step is to create an invoice, by owner, for every payment due. I don't want the inspectors to have to go through and create each invoice as the volume is pretty high (400 units each month, average of about 3 units per owner.) I'm stumped on the relationship design …

Hi, Can anyone advise how I should create a modified history relationship field ? My database as a "modified date" field which updates whenever an admin changes a records. I would like to be able to create a field which records and shows the history of these date changes. Is this possible? I know if it is yours the guys to tell me how to go about it!! many thanks..

Hello, I have 5-digit number sequences that I would like to change to dates. Here's what I'm working with: Example: 10625 First digit is the year (1) for 201(1) Second 2-digits represent the month 06 = June Last 2-digits are the day of the month (25) 10625 (needs to be changed to 06/25/11) Any help would be greatly appreciated. Thank you!!!

I'm working on a DB that will deal with projects I do for clients who hire me. I in turn hire others to help me complete the project. I am trying to figure out how to also use this DB to record expenses, some of which are project related, and others which are not. A project related expense would be paying a musician to play on my project. A non project related expense might be a piece of equipment I buy for my studio. So I could have a table called Expenses, with an "ITEM" field, where I could type in "AKG Microphone" and record its cost, and assign it a category via choice list (Equipment, Studio Supplies, etc.) But IF that "ITEM" was a musician,…

I have a simple portal in one layout of a table that contains two fields: the first is a numeric field containing the Record ID (it must be the Record ID and not the Record Number) of a related record from a self-joined table to the original table, and the second is a text field. I want to pull the contents of the portal's text field out of the first portal row (only) and insert those field contents into another text field in the same record but a different layout of the original table. Is there preferably a calculation, or less preferably a script, that will allow me to do this? GetNthRecord only seems to work with Record Numbers, not IDs and I MUST use the Record ID for…

Hi, I realize that this is a pretty stupid question but I've been staring at my database for a solid couple of days now and I've been frantically sifting through my Missing Manuel, so thank you in advance for any/all advice. I am creating a database for a photography collector, I have one "Master" table that I call Photographs which contains a unique record for each photograph in the collection. I also have an "Artist/ Organization" table. Both have an "Artist/ Organization" field, which is what I am using to connect the two tables. My goal is to be able to put an Artist's name in the "Artist/ Organization" field in the Photography table and have it automati…

I am havinga terrible mental block here. I have a table that has 2 fields, account and amount. For example 1 15.00 1 32.00 1 96.00 1 2.00 2 86.00 2 45.00 2 23.00 3 25.00 3 10.00 ...and so on where 1, 2 and 3 are the account numbers I am trying to produce a summary report layout where I have 1 line per account with the sum of the amounts... but I ONLY want to list accounts that sum to > 50 So, with this example, my report should look like Account Total Amount 1 145.00 2 154.00 ...notice no account 3 because its sum is not >50. I am fairly sure I ca…

Hi, I am having a fairly fundamental problem with getting a lookup field working between an invoice and a job table. The job table is connected via join table to the invoice one, so that one job can have many invoices. I have a PO number field that I wish to hold one number and at the point of invoice creation, I wish it to be looked up into the the invoice field lookup. I have created the fields and all looks well but data is not being copied from one field to another, when I select the field and request relookup data from the records menu. I have uploaded a Jing video of the problem to explain this in further detail. Please click on the following lin…

I'm building a database that, among other things, relates records in a CHILD table to those in a PARENT table via an ID#. (The ID# happens to be a concatenation of three other fields in the PARENT table.) The thing is, I entered 20 records in the PARENT table and about 35 related records in the CHILD table before I discovered the beauty of foreign key fields!! So anyway, I set up a foreign key field in the CHILD table. I know I did something right b/c, when I look at the 35 or so pre-existing records in the CHILD table, I see the drop-down list and can select ID#. HOWEVER when I create a NEW record in the CHILD table, I do NOT see a drop-down list next t…

I have a conditional value list question... basically, I have three tables (among many others) in my database (stripped of all other fields and shown below in colors) Timepoints, Exams, and Exam Sessions. I've created a second set of tables (below in grey) that describes the possible values for the main field in these tables named Timepoint Types 2, Exam Types 2, and Session Types 2 The possible values are hierarchical—that is, a specific "timepoint Type" value in the Timepoints table constrains the possible values for the "exam type" field in the Exams table, which in turn constrains the possible values for the "session number" field in the Exam sessions …

Hi I have a problem regarding lookup with different privilege sets. I have one table with a picture in a container which only the admin has access to, to browse, modify or view. Then I have other table which looks up the picture and copy it when the lookup constants matches. The problem is that the picture only copies when the admin is logged on but not when other privilege sets are logged on. Is there any way to work around this? Best regards, Reynir.

I feel like I'm just missing something simple here. I'm designing a simple database to register players to a non profit basketball organization. There are players and parents. A parent could sign up more than one of their children. Simple, one to many relationship. The parent information goes in, generates a family ID. Then the child info goes in, same family id of course and then a number is added to the end, e.g. 1 for child 1, 2 for child 2 etc.. The form is designed so that the Parent is on top and underneath is the child's info in tab panels for medical info, contact info, etc... What is the best way to add the next child for those parents. Would love to be able to a…

I am trying to understand the concept of one-one, one-many, many-many. This seems to be critical as you develop the structure of your database. I am not quite certain how far you take the concept….does each field need to be unique to that table? Here is an example. I have three types of customers….individuals, schools & churches. All have similarities, i.e. an address, city, state, zip, phone, etc. I also therefore have the same three types of PROSPECTIVE customers – those that I am hoping will become customers. Would I therefore create six tables to capture their unique data (name, address, city, state, zip, etc which is unique to that individual, chur…

I have a table that has transactions for a number of different entities. I am displaying the transactions on a report sorted by date and entity. Each record has an amount and ending balance. Is there a way to bring the ending balance down from the prior row (like you do in a spreadsheet)? ...I know that I can programatically scroll through and do the math. ...I also know that I can subtract the amount from the ending balance. What I want to do is just "grab" the prior sorted record's ending balance in the list view.

I am running FM ADV 11 on Mac OSX I have a table: Members PK_Members LastName etc I created a duplicate of Members called Members_Portal I want to put a portal (which shows only the Member Name) on the left of a Members form so that a user can scroll the portal, select a member and when selected, the form will change to show the detailed, selected member information. I Joined Members and Members_portal with a "X" join on their PK_Members fields I converted the portal field (Name) to be a button that has a GTRR to Members. The portal is based on Members_Portal It "Looks" good. But, clicking the member does n…

Newbie here need help please I set a job ticket up in fmpro advanced 11 I have several sales people in my office and want to set my job number field so that when the sales rep picks their name from the “sales rep field” it will auto enter their next job number. Each rep has their own series of numbers. I would like to do this so we do not end up with duplicate job numbers. My question is: Can this be done and can someone lead me in the right direction. Thank You in Advance for your help

Hi, I'm new to Filemaker, and ran into problems with some of my first layouts. I've read through the forums, and think I'm on the right track for a many-to-many relationship. So far I have three tables: Person PersonID ... Role RoleID Title ... PersonsRoles (an intermediate join table) PersonID RoleID I allow creation of records within the 'PersonsRoles' table through the relationships to the other two tables. In a layout for Person I've created a Portal. The portal points to 'PersonRoles', and shows 'PersonRoles::RoleID' and 'Roles::Title'. I can create/update rows in the portal using 'PersonRoles::RoleID'. Howeve…

Hi all, I am trying to create a Greenhouse Maintenance database that will span 4 different location sites. I am stuck at a point where it doesnt appear my database is "normalized" at all and I have repetitive data. I am going to explain how the main table (tbl_Maintenance) works so you can see where I want to go with this. Date (current date) Person (conditional value list dependent on Location chosen) Location (CA, AZ, OR,...) Greenhouse (specific greenhouse number/ value list dependent on Location chosen) Issue (generic value list across all sites) Description (free text) Currently I have this broken down into 5 tables tbl_Maintenance (w…

Hi All, I have a repairs and spare parts invoicing system that works fine but what I would like to have is a way to group similar products on my final invoice Example : In the repair process I need some M8 screws on day one Then on day three I need the same M8 screws that I enter in the Invoice. I get a new line each time an Item is added which is normal. Is there a way to detect an existing product on the Invoice and automatically add to it the similar one ? I hope my explanation is clear Nikos

Hello, Sometimes when I'm planning a database my head starts spinning and I get stuck. Can anyone help me think? I have a Product table and each product has a client name and 3 different product numbers (each means something different). I then have a Receiving-Line-Item table to check in products. Products coming in may have one of the numbers and not the others. When entering in the Receiving-Line-Item table I would love to have a portal showing matching existing data in the Product table. When entering the client, then the number of items in the portal would go down and only show those clients. Then entering ONE of the other numbers would show even fewer c…