case calculation only see first of line items

[zab]

Hello everyone. I'm glad I found this forum as I certainly need some help. Here's my first problem. I hope I can explain myself clearly as french is my first language but please don't hesitate to ask for details.

I'm building a database that look like a sale order.

First table is a list of items.

Second table that is a line item.

Third table this is called doors and windows. I have a portal in that last one with choices of doors and windows that works with the line item. SO far nothing special.

Forth table have a few fields that include data for extra cost ex: field: color_garage_door10x7 $210

Now what I want to do is add a field in my third table "doors and window" that is only a reference (that's why I don't include it in the list item) that can give me an additional amount for a garage door color if that garage door is in the order (line item and portal).

I made this simple calculation

Case (

Line_items::PFitemIDfk = 1277; PF_Variables::Extra_Couleur_Porte_Garage10x7 * Line_items::Qty;

"" )

So if the line item id is 1277 go to another table called PF_variable on the field extra... and retrieve that amount * qty

It works half fine: only for the first item of the line item (or portal) if the garage door is the second item then I don't see the data.

Any idea why it only works for the first item?

Thank you

Isabelle

[comment]

Because you are asking 'does the related field EQUAL to 1277' - and a reference to a related field returns the field value from the first related record. What you really want to ask is "does the related set INCLUDE a value of 1277':

not IsEmpty ( FilterValues ( 1277 ; List ( Line_items::PFitemIDfk ) ) )

BTW, the method seems a bit strange: if there is extra cost attached to an item, why isn't this an attribute of the item, recorded in the Items table, and looked up into line items - same as price?

[zab]

Thank you. It works.

Yes I understand and I know this database is a mess. I have to reorganise it. There is too many fields that should be rebuilt in list. I have never worked in building complex dabase before and I'm not too familiar with how to have lists interacting with lists. And that is what I am studying right now. So if you care to give me a few keywords of basic list usage that I should study to rebuild that project. It would be much appreciate.

I the meantime. Can you tell me how I can make a calculation field with the result express in another field.

field 1: I write anything into ( it can be complex) ex: 1+2

field 2: give the answer 3

May sound stupid but I need that for fast calculation.

Thank you very much for your help.

[mr_vodka]

Use a calculation of:

Evaluate ( Field 1 )

[zab]

It works with 1+1 but not with anything more complexe like 2.25*0.8.

Anyway Thanks for the info on your signature. I'll go read that to find the better way to correct my this essed up database.

[mr_vodka]

2.25*0.8 works fine, resulting in 1.8. Is your first field text and the calculation result number?