Calculation Engine (Define Fields)

Hi, I have a database (Workshifts.fp5) consisting of records containing data about individual workshifts (fields include employee ID, start date/time, end date/time, hours etc.) There's also another related file (PaymentPeriods.fp5), where users can define salary payment periods (fields include PeriodStart(date),PeriodEnd(date), PeriodNumber(serial)) There can be 12-30 salary payment periods/year depending on the user. I'm trying to figure out how to assign automatically PeriodNumber to new records in Workshifts.fp5 file when they are created, based on which used defined time slot they belong in PaymentPeriods.fp5. Any ideas how to approach this are ve…

I have been looking through this forum for an answer to my question because I don't want to post the same easy question if someone else has. But I just can't find the same problem. I have created a sales file. In it I have four tables, customer info, sales, inventory, and a lineitems table. On the customer table, I have a portal that shows all records from the line items and I have a field on the customers table that is a calculation of "sum(line items:total)". So, on the sales sheet, we can enter in the customers tel. and then enter in what they bought. But what is happening is, if the tel. is entered in first, then anything bought before that isn't calculated on th…

Hello, I was wondering how i can get a field to flash, basically i want to flash a field that show Total Hours. I want to set up when so that when total hours reach 35 it start flashing. thanks victor

Hi, I need help. I have a Solution with a job table and a line item table. Each line item has an option field in it. I have a layout with a portal that shows only the line items for the options that I have written in the Job files option field (which is on the layout. This is through a multiple relationship between the two tables (job id and option must match. What I am trying to do is instead of having to write in the options I want to view, I want to have a list on the Job file layout that shows all the Options for that job as a list and allows me to check off the ones I would like included. Is there a way to do this automatically? I hope I am not confusing an…

I have several databases: Behavior - Which collects behavior problems. Attendance - Which collects attendance information. Level Data Sheet - Which complies them to determine a "level rating" (1-3) Currently when the Level Data Sheet does all it's calculations, it is looking at every record ever entered. I would like for it to only look at the last two weeks of data (10 school days). Thoughts?

hello everybody! i'm trying to do a calculation for the period between today and a day in the past, lets say the 3rd of november. how must i calculate, so that fm is giving the numer of days elapsed since then? I use this number for a calculation later on... thanks al lot for helping! schorsch from switzerland

Hello all, Just seeking confirmation of a bug or a workaround. I am working on copying and pasting text from existing html for re-purposing. So here's the issue. I can do a pattern count and the result is positive. When I use a Position statement in a field calculation it returns 0 - no data found. I believe this is related to unicode support. Since v7 we can't choose ASCII as an indexing option so it doesn't strip the extra bit codes. I am also using a filter to strip all but the basic characters and it doesn't work either. Not expecting a quick answer. here's a sample file. TheBiz.zip

I need to be able to store video (.mov or any format for that matter) right into a container field and not just the link so I can assign control over who is allowed to see it with out them browsing around the folders and finding the video files directly. The problem is unlike other Quicktime formats that give the option of saving just the link or the whole file, video does not. Does anyone know a workaround so that the video can be played nativly in filemaker? Thanks in Advance Charles

Hello, I was wondering if someone can help me out. Basically i want to find out what the percentage of a field is. i have 3 field is call enroll and the second one is all NonStart and third one call Total. Now i want to find out what is the percentage of the total. Thanks victor

An example would be the best way to describe what I am trying to do. Let's say I have a database with 100 records. each record has a classification field. the first ten have A in that field. The next ten B. The next ten C and so on... All the records also have a calculated field. I would like to know if in another layout I can have a field that would give me the sum of the calculated field for each of the classifications (A,B,C etc...) so the sum of all the A records, the sum of all the B records ...... so I can sort them by amount. I have managed to create the sum but for each individual group. I need to have them all in one screen. Any help is much app…

So I created a pretty cool object management system in FMP7 for PC's, but Mac's obviously can't view objects, or even insert objects. They can only insert files and then export the file out. Is there any way to open that file up with it's associate program, like word, excel, etc.? Double clicking doesn't work, you can't drag it to the program, it just doesn't seem to work unless you export the field contents out. Thanks!

OK here is what I need to do! I have a ledger report that my client wants to use but he wants it to be able to show weekly stats in landscape per employee. I got it in landscape and did all other changes just cannot get it to do the calculation that would equal the total per employee. All totals I put in page does whole report or does not function at all. I am sending the file unlocked look at it and tell me what I need to do. Password is "master" Thanks The Mad Mage LBOLEDGR.zip

My problem is when I create new record in date 25 to 31, they can automatically show the message i want? any idea?

Hallo everybody, I'd like to "dinamically" update a global date field with the current date; dinamically means that none has to click or digit or do nothing at all. The idea is the FMPRO itself should check the system date and update a field with the changed value, if that is the case. This has to happen continuosly, so that if while working, with the DB open, I'm passing the 23:59, my "gCurrentDate", so to speak, will have to have stored the correct value, without me having to update "manually". Any solution which doesn't possibly involve developing a plug in? I've tried looping a script, but it locks FMPRO. Thanks in advance, and I apologise for the lack of c…

Hi, I'm creating a psychology experiment that requires that stimuli (different pictures) be presented in a random order to each subject. Subjects rate each piture as they appear. I assume that I should have a field for each picture, but can't seem to figure out how to present them in a random order. If you have any suggestions for how I might do this, I would really appreciate it! If the seperate fields thing isn't the best way to go, I'm open to suggestions... Thanks! Johanna

I have a text field made from parsed text that has a date in it in mm/dd/yyyy format. I'm having a heck of a time getting that into a date formatted field. I've tried set field which results in a "?" and i've tried a calculation field that ='s that text field as a result in date. Same result. In short Text field of "01/01/2000" set field into date field results in "?" Text field of "01/01/2000" in a calculation field with a rest of date results in "?" Any ideas?

Hi, I am trying to set a field with the window mode so my users won't enter find criteria in browse mode and visa versa. I set a calculation that stated the following: Case( Get(WindowMode)=0;"BROWSE"; Get(WindowMode)=2;"PREVIEW"; "FIND") It shows the proper message in browse and preview but nothing in find. Am I doing something wrong? how can I fix this? Thanks in advance

I have a proposal table (FM7) that adds up & totals all the work I'm doing. However it dosen't subtract. If I set a discount like -$100.00 it subtract it, it adds it? My calculations are: TotalLine Calculation = Charges * Footage then _cTotal Calculation, Unsorted,= Sum(Details::Totals) Can anyone tell me what Im' doing wrong?

Hello all; I have two tables in a database, ORDERS and CLIENTS. Each record in the ORDERS table is unique and specific to one of 18 publications in which our clients buy advertising space. Each client is entitled to a different frequency discount level which may be different in each publication depending on how many ads the client promises to buy in a given 12-month period. Now for example, when I am creating the order for client A in publication 1, I would like to have the correct discount rate automatically entered. I created a third table called DISCOUNTS with the fields PUB_NAME; CLIENT_NAME; and DISCOUNT_LEVEL. In the "Discount" field on ORDERS …

Hello All, I'm building a summary report. The break field has 5 values, Cash, Cashier Check, Money Order, Mastercard or Visa. The summary report breaks these values down into a total dollar amount for each. I now would like to add the summary values of Cash, Cashier Check and Money Order, then add the summary values of Mastercard and Visa. I tried the GetSummary function, but it doesn't get me what I want, or I'm not using it correctly. Any thoughts? Thanks in advance, Lisa

I hve unstored calculation that won't change even if I add a Commit Record or Refresh Window. Here's the deal: each contact is assigned a rep. This rep can select 15 'special' contacts. I need to display the rep's count on contact form so they know their count and can decide whether to add the current contact to their special list. I created Contacts selftable called Special and joining rep to rep. I have field in Contacts called Special (just a number field). I put Count(Special::Special). It works but if I click button to add Contact, it doesn't change until I go to a different contact. i think its because its a related thing, but that's why I put the…

hi there, I just started using FM pro 6 about a week a go. I created a DB Now I want the key field to be an Auto number, so I go to the field options and set it to "serial NUmber" START 1 and incurment as by 1. This works fine we I use it in the browse mode, however when I go to I.E. to access the database this does not work. I tried this on two different computers the same issue. ALso I haveing the same problem with a script "I made a button that put the data auto in a given field", again this works fine in the Browse mode but when I go to I.E. or even Firefox the script does not work. Your help will be greatly appriciated. thanks

Hello I was wondering can guide me to the right direction basically i have db. i am creating a report to find out what the total are for each program base on the status of the record. I got the part of the report to work where i can find the total for all the programs but not for each individual program. Here are the fields i have: c_pending , Calculation, Status = "Pending" c_cnt_Pending, Summary, =Total of c_Pending c_applied , Calculation, Status = "applied" c_cnt_applied, Summary, =Total of c_applied. I have layout in which i create parts: sub-summary by FullProgramName and also another part Trailing Grand Summary. any help wou…

After reviewing many posts here, I now understand the limitations to using repeating fields, I'll know better next time. I have been banging my head against my monitor all day trying to figure this out, please help. I have three repeating fields that represent information about a product: Item #, description and color. I need a global field to display what the Item # is for that line regardless if the current active field is Item #, description OR color. Thanks a ton.

Hi, I've just started with Filemaker 7 and developed a useful database at work. Now I would like to improve it but am breaking my head over how to do it. I haven't yet come across a similar topic in books or this forum. Thanks in advance for any help. I work for a company that buys ad space in 18 different publications. My current table is called "INSERTION ORDERS". Each record in this table is 1 order (like a purchase order) for 1 ad in 1 publication. The order includes information which determines the Gross Rate, such as: "PUBlication NAME", the "DAY" the ad is to run, the ad "SIZE" (Full page, half page, etc) and if the ad is "4 COLOR" or "BLACK and WHIT…

Wondering if there is a way for an FMP 7 container field to display an image (TIFF or JPEG) and use the ICC Profile that is embeded in the image. We have an image database that displays images but the color does not match the actual Photoshop image. A plug in would be fine if there is one out there.

Hello, New to this place, and fairly new to FMP, so pardon the question. This question seems simple enough, but I cant figure it out. I have two fields, one named "Results" the other is named "Comments". If Results = F then Comments cannot be empty. Right now i have setup to where if Results = F then the user is sent into "Comments" but I dont want them to get out of the record without actually entering comments. So: If (Results = "F".........I dont know how to do the Then part. Thank you for your time, interest and help.

Hi every body... How can I know wich of my records are the primary record to lookup others??? I have a Patients DB, in this I lookup almost 15 fields, at same DB and its related by MotherName field, when I create a new record and select the mother's name, lookup all the fill fields. But some times do, and some times no, could you help me with these?? For these reason I asked first how can I know which of my records are the primary record to lookup others??? I hope (like many times) your help. Thanks Jorge Soriano

Hello, I am trying to make a script collecting data from various records in one DB and copying them to a repeating field in a single record of another DB. Hence the repetition number will vary. So far as I can tell I can only set the repetition to a fixed number while defining the "Set Field" script-step, not a variable that is defined elsewhere. example: DB 1 (pricelist file) #1 0x pots at $40.00 #2 0x pans at $40.00 #3 1x fork at $2.50 #4 2x spoon at $3.00 #5 0x plate at $5.00 DB 2 (quotations file) #1 1x fork $2.50 2x spoon $3.00 The #'s represent records in the DB. So in DB1 there are 5 reco…

Our sales get paid differently depending upon whether it is the first-time order; or the first time the customer has ordered a specific product. Can I get ideas on how to approach this. I've been trying to think how to do it for hours now. ok. for month of October, I can find invoices for cusomter. But how do I know if it's their first order? I can count them and if 1 it's fine. But if they ordered 3 times in October, only their first October invoice would couunt for 'new commission'. And how do I separate them from a 3 count where 2 might have come from a prior month and only count their first in this month as 'new commish'??? If I can understand how to approach…

Hello all, i have a Question regarding the below Calculation. It is a Field where i want to display a Profit in Percent. If the Percent is below a certain Value, i like it to be red. To make this work, i had to define the profit_percent Field as a Text Field, otherwise the results would appear, but only black not color. However, if i make it a Text Field i cannot Round anymore to two Decimals. Does anybody know if i can fix it by using appropriate GetAsText or Number Functions? Only using the Round Function wont work. Case ( IsEmpty ( sale ) or IsEmpty ( cost ) or IsEmpty ( min_buy_break ) or IsEmpty ( min_sale_break ) or IsEmpty ( sale_cur ) or …

I can't figure out how to validate a date field to ensure that the date entered is within the range 90 days before the current date to the current date. Any ideas?

Hi- I am working on designing an orders database. This will include several different possible order item types. Ideally I would like to have one main data entry layout that would include all possible fields. Depending on what item type is ordered, is there a way to only show the relavant fields? There are several fields for a given item type that would serve no purpose for a different item type. My current thinking is to create additional views for each item type and have them mask the main view, showing only the relevant fields. The main problem I see with this is that what if I want to view all records, regardless of item type? Is it possible for the records …

Hi All, Based on user input i would like to change the color of a date field. I thought i could just put the function below in the "auto-enter" portion of my dte_finish field but it does not work. I used GetAsText because i think the TextColor function only works on text and not date fields. TextColor ( GetAsText ( dte_Finish ) ; RGB ( 255; 0; 0) ) Help please!! --Dan

Post deleted by Kicker3

Hi, I'm a newbie coming from MS Access so I'm used to writing queries. My question is I don't see how to count the number of occurrences in one field. For example, in one field I have a drop down selection of 16 choices. I want to be able to total how many occurrences of each of the 16 choices happen in one field. (i.e. 5 reds, 4 blues, 10 oranges... all in the field categor "color"). In MS Access I could write a query to do this and it would be easy... in FM I am still getting used to the Find... (which is nice to do certain things) Thanks... Vandy

if i have a field that looks up the contents of a calc field in another file, can i somehow have it update automatically when the calc result changes? i know i can use relookup, but that requires an action, and a live calculation is no good, because i want to use the field for relationships and then i can't be indexed.

Hi. I have a field that's a number, with an Auto-entered calculation. How can I get it to update, whenever the data that it's making the calculation on changes? I know if I changed it to a calculation field, it would update. However, it would also replace all previous values in that field, and since we used a different calculation formula in the past, that would change all our old numbers, and we can't have that. Thanks for the help! -Emery

Hi everyone, sorry to pester. I really try myself first (hours and hours). And i found posts on quarter but they aren't doing it like I am. I have MoYr custom function and Fiscal (year) function. Our fiscal starts 10/1. MoYrFiscal calculation displays like this: 102003 112003 122003 012004 etc. Sorts wonderful. I join these in many ways for portals and splitting up the stuff. i understnd left/right splitting and its easy to work with.. I cant change it now and it actually works well. but now they want the quarter figured also so it displays like: 102003-1 112003-1 122003-1 012004-2 I figured this out but it doesn't display quarter four, i…

How do you set the contents of a repeative field? Example: "PartNo" "PartNoAlt" Set "PartNo[2]" = "PartNoAlt" TIA Ivan

Hi All, Is there something different from FM5.5 to FM7 with summary fields?? For some reason my PO_subtotal does not re-calculate the Line_total in the portal. To get the amount to "refresh" i must move to the next record and return to the original one then i can see that the totals have been re-calculated. What has changed?? This same set-up worked in FM5.5. Thanks --Dan

I'm a 3rd year OB/Gyn resident and I've created a simple patient db. I have two tables -- patient info and pending issues. The patient info includes the usual stuff (name, resident physician, complications, etc.). The issues table includes pending labs and/or follow-up tests, therefore each patient can have several "issues". End users can add "pending issues" for a patient via a portal from within the "patient info" table. What I would like to do is create a field which represents the most recent modification date for a patient, i.e. either the modification date for the "patient info" table or the most recent "pending issue" mod date for a particular patient. I …

I am trying to insert quicktime as a file rather than the movie itself in efforts to not slow the database down... when I go to double click on the file, it won't open... why? Vandy

I am sure my project is too big for my level. Here is my problem. I am attaching a trial database. Each record has manhrs, and a start date & Finish date. The Manhrs are distributed linearly to simplify. The idea is to ditribute all the manhrs summed in a summary, distributing throughout the year on a weekly basis. So that I can draw a Curve for the Manhrs requirement. I am not sure which way to go. Thought of creating Global fields for each week of the year and going through a loop to check each record and adding the weekly data from a record, depending on whether the week falls between the start date & Finish date, add weekly manhrs. Thats very complicated..…

I have a table that has a "PartNO" field and a "AltPartNo" field. I want to use a repeating field to do a find, looking at both the same time. The best way to do this is use a repeating field for "PartNO" and put "AltPartNo" in the repeat. Question is "How do I populate the 2nd part of PartNo" with "AltPartNo"? TIA Ivan

Howdy! I'm using FMP7 on MacOS X, 10.3.5. I have a bunch of URLs for which I want to access their web pages, convert them to PDF, extract their text, and put the URLs, PDF files and extracted text into a FileMaker database. Using AppleScript I can make a droplet upon which I can drop my URL files; and it can drive Safari or Explorer to access the web pages and "Print to PDF file" and "Save as text file". AppleScript can of course then open the URL files, extract the URLs and copy them into FMP fields, and can open the text files, extract the text and copy them into FMP fields. So far well and good. But I can't for the life of me figure out how to get A…

I have a portal with units belonging to a company, every company has one or more units in the portal. How can I define a field to show all the company units in the portal?

I am a newbie in File Maker Pro. I am using FMP6 on Mac OSX Panther. I am designing a billing system for friend in a creative business. In his invoice, he needs a field against the name of his client indicating how many invoices he has raised for that particular client. For example if my friend has raised 10 invoices (4 on TOM, 2 on DICK, 3 on HARRY and 1 on GEORGE) and the eleventh invoice is raised on GEORGE, Then in this invoice my friend wants GEORGE-2 indicating that he has raised two invoices on GEORGE so far or if the eleventh invoice is on TOM then the field should indicate TOM-5 indicating that he has raised 5 invoices on TOM so far. Can this be done? …

Folks I'm pretty good at most things, but field validation by calculation is not one of them. I just cannot get my head around it. Perhaps validation won't work for what I want to do, in which case I'm open to suggestions. Users enter a code (like "REG") into a key field called "Group". This field is used as one side for a relationship. I'd like to set up a system whereby if the record is not in use (has no related records based on the key field) then the value in the "Group" field can be modified. However if the record has been used, I want the users to be unable to change the value. i was hoping I could do it with validation: I don't care if the user…

OK, this has probably been answered before, but I can find no answer that fits (I may not be using the right search terms!). Because IWP is limited to displaying 5 records in "List view", I want to tell web users how many records are returned and which of these they are looking at (say "You are viewing records 10-15 of 20 results"). Can I do this without creating a new field for this purpose? Any help gratefully received.

We need an unstored calc called LastContact in my Contacts table that always displays which date is the newest (closest to today) between: Call Logs table - CallDate (date field) Contacts table - LastReach (date field) Invoices table - InvoiceDate (date field) Ive trying multiple case statement but it seems very bulky and unnecessary. I'm already tired. It looks like this roughly: Case(Last(Call Logs::CallDate) > Last(Contacts::LastReach) and Last(Call Logs::CallDate) > Last(Invoices::InvoiceDate); Last(Call Logs::CallDate ... well, you get the picture. That's only one of, ummm, how many possibilities? OH sorry. They are related like t…

Waiting for layouts to calculate before displaying results, the dread of switching to sub-summary reports, and slow layout switches leaves me cold. Here is my sin ... I sometimes write totals to records during the night so staff can have their instant displays. You see, our company wants it all ... every summary by Contact by Product by Sales Rep in multi-column portals by day, week, month, year ... on the fly - any time they wish, filtered as they wish. And they want these summaries instantly on form layout. And I want to give it to them. I don't mind Aggregate functions on smaller datasets but my 400,000 lineitems is a bugger. Am I the only one th…

How can you hide the 2nd part of repeating field. I only want one line to appear. TIA Ivan

Trying to compile a list of "questions" (text field) and images (container field). I select the questions that I want to print out, however if the question does not have an image (ie an empty container), I do not want a blank container to appear on the printout. The "sliding" function works fine for the text field, but even though the container field for the image is empty it still prints a blank container ie it does not slide a container. How do I leave out empty container fields from my print out?

I am trying to create a field that will auto-enter the text "valid" or "Expired" based on a date field in my database. How should I do this? I tried using a calculation IF(expdate>=12/1/2004,"valid","expired"), but that doesn't return anything into my field. Thanks for any advice!

I have two time fields, start time and end time. I want to have a third field that calculates the number of HOURS in decimal format. So if the difference between the time fields is 1:30, the calculation field would be "1.5" Any help?

Hi, I bumped into annoying field behavior that I haven't been able to figure out. I have several time fields (hh:mm) in a portal. When any of the fields is being edited, the visible field contents changes to hh:mm:ss, which makes editing quite laborous. Is there any way to avoid this? Additionally, in the same layout there are sum fields calculating totals of these time fields (using a self-join relationship). The problem is that these sum fields don't update in real time when the values in the time fields are changed - only when the the sum field is activated either with cursor of tab-key the correct sums are updated into sum fields. I'm looking for a kind…

A question popped up in my head today. How does globals work when multiple users are in a solution at the same time? In my solution I have multiple tables. One table contains all the users, another one contains a lot of "active data" stored in global fields. Each user has an ID and when they log in, that ID is placed in a field in the "active data" table. All the other tables are linked to the "active data" table through the ID, limiting the users to only see their own records. What happens if multiple users are on the solution at the same time, will the global value change whenever a new user enters the solution and then giving all the online users acces…

I am wanting to include sounds in a file accessed on a cross-platform network and am finding that different users are being asked to locate the sound file each time. Is there any where I can store the sound so that any user gets instant access to it? Mike Owen

When we view a customer our sales wants to know the time difference. I have zip codes and area codes (and state and city) for the customer. How do I calculate their time zone? Would I use a calculation? to display above the customer name. The conversations look like this in the office ... "okay, Joe is in Hawaii. What's their time?" Someone will say "I think they are 3 hours behind." Someone else will say, "so it's 9am here so it's 6am there? or is it 12 there? What is behind and what is ahead mean?" See my problem? it doesn't need to update or give the exact time - just give them an idea of the time difference bwtween us. IS this possible? I have no idea ho…

I have read a lot concerning images in containers, where to store, reference or not. Here is my situation and I am looking for the best solution Single stand alone computer. Will have approx 500 records with three thumbnail pictures per record and three high res pictures per record. Planning on thumbnail pics to help speed loading. Selecting the thumbnail will load the corrisponding high res pic. I am not planning on taking this database anywhere but may in the future be shared including the pics. Then there is the possibility of moving it to another computer. With all that what is my best option in storage location, reference to imbedding pictures? AN…

Each record in my mediation log has fields for ID, date, family, month. For any one family there could be 8 ID's associated with the month BUT those 8 ID's might only represent 5 individuals. I can write a formula to count the 8 but can't conceptualize how to count the five. I'd like to print the results by month using summary fields if possible as follows: Month total individuals Family A 8 5 Family B 12 8 any ideas?

Hi, I'm workig on a dB solution and want to use repeating fields for the application at hand. I normally make a seperate dB which holds a single record for each line of a quotation/invoice/whatever, but this is kind of redundant for what I want to do now. I want to copy selected info from different records in file X into a repeating field in a single record of file Y. So far so good, no problem in doing that, but... In the layout of the new dB I only want te repeating field to show about 5 lines/values (for example), yet this field could in actuality contain 20, 30 or 100 values. My problem is that I can't find a way to scroll through the repeating field…

Question for you all (surprise surprise

Hey, there -- I'm using FMPro 5.5. We have a database used to manage our mouse colony with >70k records. My problem is, I've created a field to give a current age for each record in weeks [(Today-birthdate)/7]. Works fine, but the value is recalculated every morning the first time filemaker is open. And with 70,000 records, this takes a long time. (~15 minutes, according to the coworker who pointed this out to me.) How can I reduce this problem? I'd like to somehow limit the calculation to only those records where a certain field has a certain value. Something like "If 'alive?' equals 'Y', then (Today-birthdate)/7" but I have no idea what the language for t…

I have FieldOne with values A, B. I have two other fields called BlockA and BlockB. I am trying to write something that refers to either BlockA or BlockB depending on the value present in FieldOne. Something along the lines of: do something to the field called "Block" & "value-of-FieldOne". For example, if the value of FieldOne is B, then I want to do something to the field called "Block" & "B", which is "BlockB". Can anyone help me to write a calculation in FileMaker7 that refers to a field in this way? Thanks for the help, Michael

Hi, I have a weird issue with some global fields. I use a handfull of globals to store VAT percentages etc across my db files. For some reason, some globals retain the data I have entered while others are reset to _blanc_ when I shut down and restart. It is not dependant on type (text/number) or other settings (they are all the same). My application runs on FMP6 and is hosted by Server 5.5 on a Mac running OSX. Anyone know what's going on? NB: Yes, there are records in the db that holds the globals in question. Jim

Hi, I have photos of my pupils labelled in iPhoto, but can't cut and paste or drag and drop them into a container field. if I use insert picture I can find the photos, but they have names like 12309867.JPG which I can't match up to individual pupils unless I open each one to see if I can recognise them. I have about 300 pictures to import this year, any time saving tips please?! cheers

I'm creating my first DB, I have 2 tables one called Clients and the other one called Invoices. I made a relation from Clients.ClientID to Invoices.ClientID. Then in the layout I made a portal to show all invoices for each client and being able to add more and edit them. It works fine. The problem is I want to have a serial number in each invoice made of 3 parts ClientID-InvoiceNumber-Year (being Invoice number the number of invoices for the current client and not the total number of invoices) I also would like to be able to show a number next to every invoice showing the invoice number for the current client. Just like this: Client 1 Client…

Hello, i was wondering can give me a hand with this. basically i have a field call Status which is a drop down menu. The list of value is Applied, Pending, Enrolled, Funded. I want to find out what the total is for each of those values. I am doing a report base on those value for each program a student is part of. the database is student database. if you have any questions, please let me know. Thank you victor

I have been creating databases that include links (in container fields) to PDFs. There is one PDF for each record. I then often make run-time files that also include the links to the PDFs. I am having problems getting the PDF to open in the run-time. Sometimes it works, and sometimes it does not. I need to copy the run-time to CD, or to other computers. I include the linked PDF in the same directory as the run-time.exe file. In some cases, the runtime appears to search the directory containing the runtime because it finds the PDF and opens the file. In other cases, I get a (Not found) error, but the PDF is still in the same directory as the run-time. I am assum…

Hi all! I am new to the boards. but was told this was the place to come if I want to learn more about Filemaker. I have been using Filemaker since version 4 for basic things, some forms, some invoice-type calculation layouts, inventory management etc. But now I am working on something more involved and I have hit a wall. Here is my problem. I have a customer who wants a asset management system developed (which is no problem - done a couple of these), but the problem arises when it gets to serial numbers. The customer is a graphics and printing company who inventories printed goods from 1000's of different customers. They would like each customers inventory t…

Hi. I have created a running counter to keep track of which record I'm on. Since my file contains multiple account numbers, I first created a self-joined relationship called "self," where account number =::account number. My counter, then, is a number with an auto-entered calculation that looks like this: Count(self::account number) It works fine while I create new records. The counter dutifully enters the correct number of the record I'm on, after I enter the account number for the new record. So the 5th record with account number "3443" would have a counter value of 4, while a new record with a new account number yields a value of 0 (I know it doesn't start at one…

This seems like a really stupid question, but I've never worked with container fields before now. Once I have data (photos) in a container field, how do I find records that contain data in a container field?

I have a calculation field that I want the result is time. I have an ID number for each record, I want the calculation to take the ID number, look at the last digit and put in a time in this field dependant on the last digit. In my calculation I have: Case(Middle(ClassID, 6, 1)=1, "10:00", Middle(ClassID, 6, 1)=2, "1:00", . . .) This works if the result is text. As time I get unusual results. A time value is entered by the values are extremely off and there appears to be no pattern as to the time difference. I have tried entering the time in different formats; hh:mm:ss, hh:mm, military time, with and without am/pm, all with no success. What am I d…

Does anyone know how to set the file name reference for an image container through a script or calculation? I can get the file path of an image that is stored as a reference through the function: GetAsText(ContainerName) but I'd like to change the path and automate the choice of image to be displayed. The obvious advantage of this is that it would allow FMPro to play nicely with iPhoto or other photo repositories without having to import images. Thanks!

Apologies if I'm posting in the wrong place and apologies again if this is something which is obvious and I should know. I have 2 fields, one called "do they want to buy it" and one called "why not" What I'm trying to achieve is this; if "do they want to buy it" contains "no" then go to the field called "why not" I know this is easily achieved via a script attached to a button for example, but how can I do it "on the fly", that is when the user tabs off the first field. Any help is very much appreciated. Thanks

I imported a column from Excel into my database which had about 250 entries (no problems with that part) but when I scroll through the 250 entries in the FMP Database there are about 2500 additional blank pages tacked on at the end (pages 251 & on). Is there a way to clean up the DB and remove all the blank pages so my people are only able to scroll through the 250 that have information? Sorry for the ignorant question here...

Hi, Can a container field hold a BLOB file? This file type(BLOB) is used to store signatures on PDA's. And I would like to transfer them into a container field. Thanks

I want it all. Find, sort, use in joins, reports by month/year category. I want to only have one in custom function (i think). I want it to be reused for many things and mahy different dates. I keep running into problems in this. I want: 112004 122004 012005 So it sorts right. But I can't get zeros. And I don't know if it should be number or text. I want to use this same date style for joins to real date fields but, of course, I can't. I wish I could have this perfect month/year as a Custom Functions so I don't have to redo it each time I need it, which is constantly in every table it seems. And I want it available to break apart to get year easily w…

I'm sure this is basic, but I'm completely stumped and nothing I have found by searching has caused any lightbulbs to appear above my head, so please humour me! I have a database of tapes, each of which may contain several ads. The tapes are numbered (from 00001) and the ads on them should be numbered, too, starting at 001 on each tape. I've created a separate database for the ads and put a portal in the tape database. So far, so good. I just can't find a way to automatically number the ads on each tape. I've only been able to do it manually and the addition of an ad number creates the record in the ad database. But there must be a way to do this automatically..…

Date is date of current entry. DateStart is a static date in the past. How do I create a calculation that will show the Year Number of the Date based on DateStart? For example: DateStart = 1/1/00 If Date = 1/1/00...12/31/00, Year = 1 If Date = 1/1/01...12/31/01, Year = 2 If Date = 1/1/02...12/31/02, Year = 3 Thanks!

I need some of the expertise in this forum. I'm trying to setup a database that will be somewhat similar to the Business Tracker in theory but extended somewhat. I have customers and products and invoicing and payments. This is a port operation that has ships that come into different docks for different customers and either load or unload certain chemicals (products). What I don't know how to do is set up the rates for charging the customers. We charge two items... wharfage and dockage. Wharfage is based on the amount of product loaded or unloaded, i.e. 4.76 cents per barrel for oil and 2.13 cents per barrel for all other chemicals. Dockage is based on the length of the…

Long time listener, first time caller... Blow is a description of how we use the database, and the additional function I am hoping to have while using the "image:Images/*.jpg" Formula. USE: We are a 3D production house in the middle of developing a video and at the moment all of the artist open up the "Asset Manager Database" to see what shot number they have been assigned and then go to another directory to open up the corresponding storyboards image to see how the shot needs to be animated, lit, etc. (this waists a little time because each person needs to open up 2 different things to look at for a single shot) What I would like is to have the Shot ID and …

How can I create a calculation field with the same name as the database. Only showing the name of the database, not all the databases that are open. Many Thanks Steve

This is a follow-on from the "Insert Blue Date Before Comment" posting as it was suggested that I start again with my questions.... I have to contact potential new clients as part of my job and all details are recorded in the database I'm developing. The action is recorded so that I can generate various reports. What I want to do is add new action to the action log without losing previous actions listed. I've attached the basics of what I'm working on with the hope someone can help with the final bit to make it work. As you can see, I complete an action in [action log::latest action] and enter the date in [action log::date of latest action]. I will be…

I don't know if I can explain this, but here goes. I would like to find duplicates that aren't officially duplicates... specifically, I need to find records where the first 5 alpha/numberic characters in a certain field match and the sixth digit is a match defined as 1=4, like this: 123451 123454 3C5671 3C5674 Basically we discovered that thousands of records were entered twice, so now we need to isolate them, reconcile the inventory and correct the database. Is there anyway to accomplish this?

The Customers DB record contains a Telephone number and name/address information. The Invoices DB record contains a Telephone number, as well as name/address fields that are defined as Lookup from Customers based on Telephone. When I add a new customer, I start by typing his tele. no. and name/address in the Invoices record, and clicking a button to drive AddCustomer script. That, in turn, drives an external script in Customers which creates a new record and populates it by reading the relevant fields from the current record in Invoices. So far, so good. The external script then pauses to display the newly-added Customers record, allowing it to be doublechecked a…

I'm trying to make a simple voting database for my school. I have 4 fields, one for each pres. candidate and two more for the student to indicate boy or girl. All of the fields are checkboxes I made them using a simple value list that just says "yes", but the students only see the check box. I made it this way so that our older kids could do some data collection after the voting (i.e. boys vs. girls voting for Rep. or Democrat). Two questions: Is a better way to do this, but more importantly, how do I prevent a student from voting for both candidates by mistake? I know there must be a way to have a pop up message come up that I could customize to tell students t…

I'm working on a staff database... each staff member is assigned a department (via a drop down list field) and a shift that they work (1,2,3 or 4) I want to make a layout that will list each department and the total number in each shift... Iv'e tried varius ways of defining calculation fields (sum, count) and I'm just not getting it to work... Michael

I'm working on a membership database. I have three primary tables: ClientList, ClientCards and ClassAttendance. Every time a new class begins, I create a record for that class, lookup clients from ClientList, enter them into the attendance roster for that class and at the same time, lookup information about the current membership status from ClientCards for display. This allows me to see current membership status for each client as they check in for the class. When a card is expired and they buy a new class card, I don't want that card's information to disappear so we can keep track of the history. I'm not sure the best way to handle having multiple card records while onl…

Hi, Sorry if this is too basic a question, but I've looked all over without finding an answer... I have a database where one image is associated with each record, and I want to be able to make a layout that includes a preview of this image. Is there a way to include only the path in a field in the database and then have it automatically display the image at that path in a layout? (I would like to be able to easily make changes to the images, and not have to worry about the database getting too big, that sort of thing, by keeping the images separate.) Any ideas? Thank you very much!

Does anyone have a calculation that will conver double barrel surnames to proper case - for example Smith-Davis (not Smith-davis) also O'Neill not O'neill.....there maybe others but has anyone got a solution that handles all these? Thanks

Is there a way to limit access to certain records based on the logged in user? Example: I have a grade database with 2 sections of a course. How can I give 1 TA access to one of the sections and a second TA access to the other section, without having to worry about 1 TA changing the other's grades?

Hi, I am new to this list ... but hope you can help. I am trying to split a length of text into a series of 4 lines 30 characters long for printing on a label printer. Words should not split over lines. I am using a mixture of length() and wordcount() functions, etc... which does work However, the calculations seem not to recognise text with a "," ";" "/" or other character at the end of words. For example: LeftWords(Credit Text, WordCount(Left(Credit Text, 30))-1), LeftWords(Credit Text, WordCount(Left(Credit Text, 30)))) where Credit Text = "Helen Turnbury, Designer: Christopher Tompkins" this code returns "Helen Turnbury, Designer" dro…

Ok I have darn near rewritten this program that was left in my lap 30% done but some where in this program in the 30% that was done is a Global field that is setting all text background color. It says it is set by Color Trim. I followed that to a statement that said Color Trim = Trimcolor number so I went to the trimcolor number but there is no way to enter anything in the global number field. So I am at a loss and since it is one of the last things left to do I could really use some help.

Is there a way to concatenate data for a Portal such that some of the text will be bold or in italics for instance.. I have a portal setup that show the Clients name <<<BOLD The Client's Address <<<ITALICS and then Client notes in regualr type... I would like it to appear as such CLIENT <<BOLD Address <<italics Notes...notes notes.. did I make that confusing enough?

Hi Guys, Im writing a module to export some data into an accounts package. Im trying to clear certain fields of "illegal" characters, before my records are exported as a CSV file. I am using the substitute command to clear away symbols like !,",

I have in one file the Account Name and Part Number. Substitute ( Match CM Name & Match Part ; " " ; "" ) In the other file I have a calculation from 2 tables, because in that file the Account Name is in a different table than the part. Substitute ( DIT:: CM Name & MatchPart; " " ; "" ) So, the basics are that I need to match both the CM Name AND the Part Number, and all it has to do is flag the line item from the first file if there is a match on the criteria in the 2nd file. I've gone over and over this, and I can get the lookup to work if it's just on one criteria - like one DB to one table in the other, but when I try to do it with these…

Is it possible to display the size each container field takes up in a record? My database has recently grown drastically, and I'd like to see a list of all the records that have files in their containers, and how big those files are.