Can't get data from related file based on calculation

[zip]

I'm having problems getting data from one file to show up in another file based on a calculation.

I have the following 4 files;

Inventory

Customers

Invoice

LineItem

I need to be able to provide custom price based on my customers.

My products(inventory) is compose of 9 different item number ranges each with 7 different size. Example: 1000 to 1999 is one range with 7 sizes. The next range is 2000 to 2999 with 7 different sizes. All the way to 9000. Ecah range and size has a special price based on customer.

I created the following fields in the Customer file. Each field contains the price.

For the 1000/1999 range the following are the fields for the 7 sizes,

c1,f1,g1,p1,pot1,h1,cb1

For the 2000/2999 range the following are the fields for the 7 sizes,

c2,f2,g2,p2,pot2,h2,cb2

all to way to 9000/9999.

I created the proper relationships between all four files.

Problem is I can't get the price info in the Customer file to show up in the LineItem portal inside the Invoice file.

Item#, Description, Size and Unit price are in the LiteItem portal inside the Invoice file.

My LiteItem has the following calculations;

Calc1 determines the range:

Case(

Item#>=1000 and Item#<=1999,1,

Item#>=2000 and Item#<=2999,2,

and so on all the way to 9000 and 9999.

Calc2 determines the size:

Case(

Unit="CP",1,

Unit="4inch",2,

Unit="Gal",3,

Unit="PP",4,

Unit="5inchPot",5,

Unit="HB",6,

Unit="CB",7,0)

Unit Price (calc) suppose to take info from

calc1 and calc2 and get price info from Customer file. This is what it looks like:

Case(

calc1="1" and calc2="1",Customers::c1,

calc1="1" and calc2="2",Customers::f1,0)

But it doesn't work...Any ideas..

Zip

[LiveOak]

You can break the problem into two pieces:

1) Are you getting the correct values from your calc1, calc2, and Unit Price selections.

2) Does the Customer relationship work so that Customers::c1 yields the correct information.

First I would probably place Customer:c1, Customer::h1 or whatever on a layout in the Line Items file and see if anything show up. If not, you need to look at the fields and specifications you are using for the "Customer" relationship. You don't mention what field you are using to connect the two files and what type it is. Make sure the connecting field is a number or carefully controlled text string. -bd

[zip]

I get the correct values for calc1 and calc2 but not from Unit Price.

I've placed Customer::c1 in LineItem layout

but nothing shows.

I'm using customer ID field of type number for my joining field on LineItem and Customer

files.

zip

[LiveOak]

It sounds like the Customers relationship is not valid or if it is valid, it isn't displaying what you expect it to display, only what you TOLD it to display. Probably a case of "something you know that ain't so". You need to work on the Customer relationship to get it to correctly display fields. Check:

Fields on both sides of the relationship need to be the same type (text or number). Both need to be indexed. If your index has special characters in it such as "-", indexing needs to be set to ASCII. Are the data fields really blank in the related record you are pointing to? Etc. -bd

[zip]

Thanks, I got it to work.

The problem now is that the invoice does not

show the value right away. I have to click once outside the portal (inside invoice file) for the values to show up on my screen.

The first line item does not show the values, but once I click outside the portal the values show up for the first line item and then automatically show up for every line item I enter thereafter.

Any ideas?

[LiveOak]

Portal refresh is a continuing FM problem. I don't know a way around clicking out of the field to allow refresh. -bd