repeating calculation fields problem

jamesooooo · August 17, 2003

OK, i have 2 databases. one is a database with a bunch of records wich hold information on parts ( partsfile ) and the other is a database that holds jobs.

what i am trying to do is have it so when i type in a part number in on repeating field that it looks up the part name and cost for that part number in ( partsfile )

now i got it to work making a relationship with partsfile and partnumber but if i change the cost in partsfile it won't update in my jobfile. so what i did was try and use calculation fields and it works fine for the first rep. but will not lookup or display the rest. i have partnumber, partname , and partcost set to 15 reps and made sure it is setup to display all 15. any ideas on how i can get this to work anyone? It works great with just using them as a lookup but i want them updated whenever information on the cost or part name etc is changed in the related file. any help would be great. i am kinda new to filemaker pro so please if you can explain it in a easy way for me or i am using filemaker pro 5.5 if that makers.

Anatoli · August 17, 2003

Use Relations. That way everything is manageable and fast. You can display records from related database through portals. Check FM examples.

BTW -- repeating fields are there since time, when FM wasn't with relations and now for backward compatibility.

It is not your fault, that you are trying to use them.

FMI should put on each page BIG RED WARNING about inflexibility of related fields.

CORRECTION after Ernst post: FMI should put on each page BIG RED WARNING about inflexibility of repeating fields.

jamesooooo · August 17, 2003

Am, i just better off just making 15 fields and 15 relations and not use 1 repeating field?

ernst · August 17, 2003

Hello James,

Welcome to the forum and let's start with the traditional -don't use repeating fields!- warning...

If you do some searching on the forum using "repeating fields" as search phrase you'll see why.

About your problem: the idea behind a lookup is that the lookup only takes place when a matching value in the key field is entered; i.e. when you enter a part number in your jobs file that matches the part number in your parts file.

When you are using a lookup to determine the prices for, for example, an invoice then this is exactly the behavior that you want. As the price of a part changes you definitely do not want all your old invoices to change as well!

So if you are in a situation where you always want the price in one database to follow the price in another database you either have to make a scripted 'refresh' mechanism, or you have to use a calculated field as opposed to a lookup.

And that's where you'll find that the repeating fields become cumbersome to work with. If you want to 'refresh' the prices you'll have to make a script that re-enters the part number in every repetition. And using a calculation will just not work in repetition 2...15.

Best thing you can do is actually get rid of the repetitions and use a portal that shows a list of records from a new file. If you look around on the forum you'll find that that

ernst · August 17, 2003

>>FMI should put on each page BIG RED WARNING about inflexibility of related fields.

I'm quite sure Anatoli means 'repeating fields'.

Buy hey, he gives 5 answers in the time that I can formulate just one....

Ernst.

Anatoli · August 17, 2003

Thanks a TON!

jamesooooo · August 17, 2003

Thank you ernst,

hmm, ok, i do want it to keep the prices matched with parts file as this will actually be a price book. and I want the part prices current. hmm, sounds like my best coice would be to just remove the repeating fields and just make 15 fields named partnumber1, partnumber 2, etc. to 15 or that refresh script you spoke about. i haven't played with making the scripts yet though so that may be a bit too much for me atm.

ernst · August 17, 2003

Hi James,

I would really consider trying the extra 'LineItems.fp5' file if I were you.

Imagine you go through the trouble of making the 15 fields (with 15 relations and 15 calculated pricefields) and somebody comes in and wants a book with 16 parts...

It may seem easier to use the '15 fields' strategy, but in the long run you quit likely will regret it.

If you need more advice on how to set this up either look around the forum or ask.

Regards,

Ernst.

jamesooooo · August 18, 2003

ok, thanks.

I will look into the line item file thing. so it would mean i would have 3 databases instead of the 2 then?

jamesooooo · August 18, 2003

hmm, i have been thinking about the line items file and was wondering if you could explain it to me. I can tell you how i have it now and maybe you can make a suggestion on how i should do it.

I have a file called bdparts which has the following fields

partnumber ( the part number )

partname ( the decription of the part )

cost ( the cost of the part )

list ( the selling price for the part )

markup ( the percent the part is marked up )

then i have dbprices which have the following fields

task ( the number for the task in the pricebook )

jobname ( the decription of the task )

hourlyrate ( the cost per hour for labor )

joblabor ( the number of hours for this task )

totallabor ( a calculation hourlyrate * joblabor )

and i have 7 repeating fields named the following

quantity

partnumber ( made a relationship to dbparts with this )

partname ( used a lookup to dbparts )

cost ( looked up cost in dbparts )

list ( looked up list on dbparts )

extcost ( quantity * cost )

extlist ( list * quantity )

total list ( sum(extlist) )

the trouble i am having is when i update my parts file the cost isn't automaticly updated in dbprices as its just a look up. what i guess i need to understand is how do i use a lineitem file to improve this so that when i update

a parts price it changes the price in my pricebook. any help would be very appreciated.

ernst · August 18, 2003

Hi James,

Just quick before I go to work...

All fields that are repeating should be in the new file. They do not need to be repeating. 'cost' and maybe 'list' should become calculated fields, you need to define a relation to 'bdparts', just like you have now from your 'dbprices'

Further the new file should have a keyfield to relate to the 'dbprices' file, I think that 'task' would be a good candidate.

In 'dbprices' define a relation from 'task' to 'task' in the new file, with 'creation of new records' on.

Draw a portal in your 'dbprices' database that shows records from the new files.

This should do the trick.

Ernst.

jamesooooo · August 18, 2003

Ok, i made a file called lineitems and i need to make a field called task,cost,list ,partname,partnumber ? and make a relationship from pricebook to lineitems using task? i am sorry i am a bit confused so far as to how this will help

ernst · August 18, 2003

jamesooooo said:
Ok, i made a file called lineitems and i need to make a field called task,cost,list ,partname,partnumber ? and make a relationship from pricebook to lineitems using task? i am sorry i am a bit confused so far as to how this will help

Hey James,

This will make it possible to view all lineitems that belong to one task to be displayed in a portal. Just like the lines of your repeating field used to do.

Try it and report if you get it to work?

Regards,

Ernst.

jamesooooo · August 18, 2003

do i still use repeating fields for the parts in pricebook?

ernst · August 18, 2003

No. The portal is in stead of the repeating fields.

Ernst.

jamesooooo · August 18, 2003

and that will work like the repeating fields with lookups except that the parts costs will change to reflect any changes in dbparts file?

ernst · August 18, 2003

Yes, but you should change the new 'costs' field to be a calculation. Its definition should be something like:

RelationToBDParts::cost

And maybe you want to do the same for the field 'list'

Ernst.

jamesooooo · August 18, 2003

Ok, thank you for your help. I have never used portals before so its a bit confusing but i am trying to play with them now. i appreciate all your help.

so i use task as the related field to point to the correct record in lineitems right? if so and there is no record that matches can i make it start one?

ernst · August 18, 2003

I suggest using 'task' , because I think that it's a unique field for each record in your pricebook.

And task points to one or more records in lineitems.

Each record in lineitems will be equivalent to a line in the repeating fields that you previously used.

To make new lineitems in the portal you should turn ON the 'Allow creation of new records' checkbox in the definition of the relation.

If you do this, Filemaker automatically creates a new record when filling in values in the last (empty) line in the portal.

Hope this helps,

Ernst.

jamesooooo · August 18, 2003

OK, this will be my first try at a portal, i really don't know anything about them.

i make one portal to line items right? and i have quantity,partnumber,partname,cost,list,task in the file line items right? if so then do i setup the fields in lineitems as calculations with a relationship to dbparts?

jamesooooo · August 18, 2003

OK, i make lineitems and put in the following fields quantity,partnumber,partname,cost,list, and task which is the related field to pricebook. now do i setup the fields as calculations related to dbparts?

jamesooooo · August 18, 2003

sorry i repeated myself. hehe i thought the reply didn't go through.

ernst · August 18, 2003

In one of your previous posts you defined a relation from 'lineitems' to 'dbparts' with 'partnumber' on both sides.

Now make 'cost', 'partname' and maybe 'list' calculated fieldswith defintion something like: RelationToBDParts::cost.

jamesooooo · August 18, 2003

I think i am beginning to understand this. my next question is how do i put a field in the portal and do i need a portal for each field that i want to diplay or edit?

jamesooooo · August 18, 2003

WOOT! it worked.. hehe i think i have it! thank you soooooo much for all your help ernst! I am going to play with a few things and see how it works out.

ernst · August 19, 2003

Good you figured it out. Let us know if you get stuck again.

Bye for now,

Ernst.

jamesooooo · August 19, 2003

Will do. oh, how do i delete a record from the portal? say i delete the contents on line 3 in my portal how do i get it to remove that record in lineitems?

right now it seems to leave a record with the task number and only deletes the contents of the other fields in that record.

ernst · August 19, 2003

You delete a record in a portal by choosing delete record from the menu, or typing backspace, op typing apple+e on a Mac (presumably control + E on a PC), all with the portal-line taht you want to remove selected.

This is assuming that you have 'Allow deletion of portal records' ON for your portal.

Goodnight,

Ernst.

-Queue- · August 19, 2003

Or you attach a script to an icon (such as a trash can) available in each row, if you aren't giving your users indiscriminate use of menu commands.

jamesooooo · August 22, 2003

Control - E did the trick. that you for all your great help.

Sign In

repeating calculation fields problem

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information