tryphe

Thank you the Trim function worked like a charm I didn't know that is what it did I am still figuring out all the new stuff on FM 7

I am trying to import data as a .csv and it is coming in with extra spaces in some of the fields. Is there a way int he import script or another script or calculation to have it delete unnecessary spaces in all the records? Thanks

I LOVE YOU no really I do I tried the portal thing and realized I had set my service code to numbers instead of text which is why the calculation wasn't working. It is all working perfectly now. Doing the happy dance singing the happy song La la la la

I know bad me I am fixing it and making a separate database with pricing and relating it back as you suggested. Thank you. I am just having one more problem the first set of calculations and look ups work great but the next pricing isn't. I made a relationship based for the first set of pricing for Colour keys it is related by Colour Key Code which is calculated based on several factors. THe look up looks up the price great. But when I did the exact same thing for the next set of prices Film and related it back to the pricing database it is taking the colour key prices instead of the film prices. The Film codes are again determined on a number of factors to get the code. But shouldn't it be only giving me the price that is listed in the pricing database for that code. IT is two different relationships so I don't even know how it is getting the Colour Key code price. My god I am just reading this over and I realize I am making no sense. I am just not explaining this well. See below for what I am doing....maybe that will help explain what I can not. Film Service code: Case(Ad Letter="A" and Colour="Black","F1CA", Ad Letter="A" and Colour="2C", "F2CA", Ad Letter="A" and Colour="4C", "F4CA", Ad Letter="B" and Colour="Black","F1CB", Ad Letter="B" and Colour="2C", "F2CB", Ad Letter="B" and Colour="4C", "F4CB", Ad Letter="C" and Colour="Black","F1CB", Ad Letter="C" and Colour="2C", "F2CB", "") (one part of a long calculation) So the service code is determined. The next calculation... Film Price: Case(Film service codes="F1CA", F1CA, Film service codes="F2CA", F2CA, Film service codes="F4CA", F4CA, Film service codes="F1CB", F1CB, Film service codes="F2CB", F2CB, "") Now Fields F1CA and F2CA and so forth are number lookup fields values imput by a relationship. The relationship is Film pricing relationship ---Film service code:price code then copy price field. everything works except it is giving me the Colour Key prices ignoring the Film service codes. THe Colour Key calculations are similar and work perfectly CK code: Case(Ad Letter="A", "CK1", Ad Letter="B", "CK2", Ad Letter="C", "CK2", Ad Letter="D", "CK2", Ad Letter="E", "CK3", Ad Letter="F", "") then Colour KEy amount: Case(CK Service Codes="CK1", Colour Key 75, CK Service Codes="CK2", Colour Key 37.50, CK Service Codes="CK2", Colour Key 37.50, 0) The colour Key fields are lookup number fields valued by a defined relationship Colour Key relationship---CK codes:Pricing then copy pricing field. It should work shouldn't it? But it doesn't the colour key works perfect but then the film always gives me the Colour key price it is the lookup relationship that is screwed but I can't figure out why. Please Help!!

Umm nope don't have a product file. We are a magazine publishing company so it isn't like that I am doing a billing based on ads supplied for a particular issue. One of the Prices is based on how many colours it is, if is new, what the size is. Another price is based on whether there is film needed or production needed. All of this info is taken from the same database that my designer fills out as she gets the ads in. The billing is just another layout in the same database. so no Product name no product price no relationships at all ...the calculations are just as I laid out in my original post. They just don't work when I use the auto look up. Should I be putting the prices in a separate database?

Okay I am so frustrated right now! I have made several databases over the last few years and just last week my boss informs me I have been doing them all wrong. Now I am self taught and my databases have all been working but I have never used the Auto entry function. My boss says I should be using it for all prices that way if and when we update them we won't affect all the previous invoices and calculations. We are talking about alot of prices. so do I really need a separate field for each price and in the auto entry data field put the price? Is that the right way to do it? I tried it with the new billing database I am building but now my calculations aren't working. They did before I switched to using the field with the auto entry data. What am I doing wrong? The pricing calculation is used as part of another calculation that reads If(ad="Yes", price calculation, 0" It all worked before the way I originally did it but not since I redid it the waymy boss said it should be done. Here is what the pricing calculation looked like before Case(Ad Letter="A" and Colour="black", 75, 0" (this is just a small sample of a very long calculation but it is all basically the same idea with "B" , 75 and such) the New Calculation is Case(Ad Letter="A" and Colour="black", New 75 auto field, 0" New 75 auto field is Number field with data check marked in the auto entry and the number 75 supplied for data. But it doesn't work. Nothing happens. AAAAAAUUGHH what am I doing wrong my boss says this is bsic filemaker and that I should know this but hey I don't. Any suggestions or help would be greatly appreciated. WHat is the right way to do a billing database on that won't affect all the past billing when prices are updated. Is he right and the way I did it was just stupid? If so then how do I do it right? And get all my calculations to work? I am so frustated right now. So much work and time wasted. Am I making any sense at all? HELP Please HELP

Thanks everyone for your help I did get it to work using your suggestions. Thankfully it doesn't actually have to be exactly a month earlier...it is just a notification for us to start calling people in advance to have them renew so if it is off a day or two it isn't a biggie.But It is working perfectly now and I thank you all for your help.

I am trying to make a renewal database. I need to put in a date that the original form was signed and then have it teh renewal field automatically calculate 4 years less 1 month from that date so if the date was 12/5/2003 the renewal date needs to be 12/4/2007 I can't seem to find a way to do this. I can do it separately by having the month and year in two different Renewal date fields as numbers but not as one field as a date. Then I am trying to write a calculation that says if renewal date is less then or equal to today's date then Renewal "Yes" else blank. I have tried it with the separate fields but I can't get seem to get the calculation right to give me the "Yes" or blank it always reads Yes. Nothing is working and I am not sure what I am doing wrong...Any ideas, suggestions, Help what am I doing wrong there must be a way to do this. Thanks

I know I have done this before but it has been a while since I scripted anything and know I am going crazy trying to get this to work...here is what I am trying to do... I want to write a script that will find me only records that have no date in a certain field...unapproved records. and I want it to omit all the ones that are cancelled. I have tried numerous ways but it keeps omitting records with the same name and member numbers even though they are different records or it doesn't find them all just some. Like it will only find 35 out of the 50 that are actually unapproved. I had written it so that it would look at the unapproved field and omit any that were ifvalid because the ifempty function never seems to work right. The cancelled field is a checkbox and if it says yes I want it omitted. I only want the records that have nothing in the unapproved field but aren't cancelled...why can't I seem to be able to get a script to work! And why does every script I have keep omitting records of the same name and member number (all the data I use is submitted to me from another filemaker database that I import into my database...could they be linking hte records? is there somewhere I can check for that? Going crazy so any help I can get would be appreciated. '''''''''''''''''''''''''''''''''''''''

What would I use instead of repeating fields? Wouldn't I create a whole lot more work for myself if I have to create 20 different activity fields and write huge long calculations for each one. What are my options?

Thanks for the reply. I realize that fields can be used on a number of layouts and have done it extensively on the database I am developing. I don't think I explained my problem exactly right. I have Activity field which is a pop-up repeating field, an Hours field which is a repeating number screen on the Time billing layout (there is also an employee text field and date field). On the production layout, which is where all things are gathered and all costs live for a specific docket, each activity from the pop-up list in activity has a field where the actual costs are totaled from that which is entered in the Time Billing layout. For instance there is a writing field, an outside consulting field, a programming field. I have made them all calculation fields, I have written the calculation -I'll use programming as an example- Programming=Case(activity="programming", Hours*150,0) which works except that it only will read the first of the activity repeating field on the time billing layout it won't add on or check any of the other 20 repeats. That goes for all the other calculation fields writing ect..if the first one doesn't read Writing even if the second or third one does it gives me the result 0. I need it to be able to look at all the entries in the activity repeating field and give me the results in the appropriate field. Have I explained this right I hope so. [ August 23, 2001: Message edited by: tryphe ] [ August 23, 2001: Message edited by: tryphe ]

I am new at filemaker so I hope I explain my problem correctly. I have one database with multiple layouts - I have a time billing layout in which to book the employee, hours, activities, I know the calculation to set it up so that depending on what activity is chosen the cost is calculated. My problem is I am not sure what field to attach the calculation to. I need the result to to be in a generic field on the time billing layout but also depending on what activity is chosen I need the result to appear on the production layout in the appropriate activity costing. So for example Employee A worked 2 hours writing on a specific docket (all the layouts are linked by Docket number)Writing (chosen in the activities pop-up menu field) is $150 per hour therefore the cost is 300. That is all displayed on the time billing sheet that is attached to the docket. Now that $300 needs to be automatically transfered to the production layout for that docket in the writing costs field. How do I get it to know which field on the other layout to apply it to? To which field do I apply the calculation since where the cost will end up depends on which activity is chosen. AM I making sense? Can anybody help me? I have been trying to figure it out for two days!!!