Trying to pull an average...

[FMchallenged]

I'm an FM noob starting to learn to develop with it. I appreciate any help. Please avoid acronyms and use many small words in any replies (thanks).

Table A is a typical inventory table with a WidgetName and several other fields describing the widget.

Table B is a VendorList that includes VendorName, WidgetName, WidgetCost. It contains one record for each widget a vendor sells. The same widgets are sold by many different vendors at different prices.

I want to get an lowest, highest and average cost for each WidgetName from all the VendorList records that show that WidgetName. Then display that information in 3 fields in Table A.

A secondary goal is to display a list of each Vendor that sells a WidgetName in it's Table A inventory record, and the cost they sell it for. So an average, plus a list of the individual prices (sorted from lowest to highest).

Essentially I want to look at an inventory record and see a WidgetName record that includes the average, high and low selling price and all the vendors that sell it, with their individual prices.

Can someone give me a step by step? Or at least a push in the right direction?

[comment]

Will you be entering this data yourself or will you be importing from external sources?

The reason I am asking is this: you need to define a relationship between the two tables. Ideally, the relationship would be based on a unique WidgetID (automatically assigned to each new widget in the Widgets table). However, if you don't have control over data entry into the other table (I believe Prices would be a better name for it), you will have to base the relationship on matching WidgetName.

Either way, once you have established the relationship, you can show all related prices in a portal to Prices placed on a layout of Widgets. You can sort the portal by price.

In addition, you can use calculation fields in the Widgets table to determine the lowest, highest and average prices, using the Min(), Max() and Average() functions, e.g.

Min ( Prices::Price )

[FMchallenged]

Thank you, I do indeed have the relationships set up and you have solved my issue!

Thank you!

Edited September 3, 2009 by Guest

[FMchallenged]

One more question:

How can I set portals so that they only display lines that have data? IE I want to leave room for 10 records, but only 2 records exist, so I don't want to see 8 blank, alternating backgrounds below the 2 that exist.

[Søren Dyhr]

I want to leave room for 10 records, but only 2 records exist, so I don't want to see 8 blank

Eventhough the question might reveal a normalization inadequacy, could Ugo's method be used...

--sd

ugoing.zip

[comment]

How can I set portals so that they only display lines that have data?

That's not possible (at least not in Browse mode). You can use less rows and add a scroll bar, or make the portal less prominent (even invisible, if you want).

[FMchallenged]

Ok, I am having another more serious issue. The portal is generally working, except in some cases it is just randomly not printing the data.

For example, I have a Vendor Catalogue table that has 3 fields:

VendorName

ItemName

ItemCost

So there is one entry for each item a vendor sells in that table. I have another table which is a Vendor List that has 3 fields:

VendorName

VendorLocation

VendorType

PLUS a portal that pulls the ItemName and ItemCost fields from the first table above to list all items sold by each vendor.

The problem is that for just a few vendors, like maybe 5 out of 125, it does not list their items, even though they have several records in the table.

I tried deleting and recreating the entries that do not show up, and same problem. So then I exported all the records out to an XL spreadsheet, and all the vendor data was there. Wiped the table, recreated it, and then imported the data back in, and had the exact same problem.

I'm using 10.0v03 Advanced... anyone have an idea how to clean this issue up?

[comment]

The most likely reason is that the names do not match EXACTLY due to trailing spaces, invisible characters, etc.

See also my note above about using meaningless serial number ID's as matchfields.

[FMchallenged]

Trailing space on those few records was the issue... thank you!!

Now how do I make the portals "invisible" I have been looking for an option to turn off the lines that frame the records, and can't find one.

Edited September 4, 2009 by Guest

[comment]

In Layout mode, turn on View > Formatting Bar. Then select the portal and set Line to "None" (0 pt).

[FMchallenged]

Thank you again!

Ok here's a tougher question... I would like to be able to click on field that is listed in a portal... and be taken to the record / layout that the field is being pulled from.

Possible?

[comment]

Set the field to be a button, using the Go to Related Record step.

[FMchallenged]

Man you know stuff!

Ok... got that working... and found out how to have it open in a new window... what if I just want it to kind of open a pop up with the info instead of a whole new window with all the record controls and such...

For example I click on a Vendor and get a pop up portal with all the stuff the vendor sells from the Vendor Inventory...

[comment]

There is only one kind of window. You can control the size and the position of the new window. You can also hide the status area (if you only want this for the new window, you'll need to do this in a script, as this would require two steps).

Another option is to simulate the whole thing with a tab control object.

[FMchallenged]

I sort of found what I want with a pop-up tool tip. I see that setting a mouse-over tooltip gets me access to the calculation window.

So now, what I am trying to do is figure out the calcuation that will create a portal list from a related table.

So I have a portal that is listing Vendor Names... and when I mouse over a vendor name it goes to my vendor product table and lists all ProductName records for that vendor.

So since tool tip only gives me access to calculation, assuming I need to figure out the right syntax... if this is possible?

[comment]

I am not sure how your relationships are set up. You have a portal to Vendors - from where? Is it using a TO of Vendors that is also related to Products?

[FMchallenged]

I have a vendor product table that is:

VendorName

ProductName

ProductPrice

I have a product table that is:

ProductName

ProductPicture

(various product features)

I have a Layout that shows a picture and description of ProductName, and includes a portal that lists all VendorNames (from the Vendor Products table) that sell the product, and the price they sell it for.

The two tables are related by ProductName.

When I mouse over a VendorName in that portal, a tooltip pops up. I want this to list all ProductNames and ProductPrices associated with the vendor name from the Vendor Products table.

When I do this with a portal in a layout, I just ask for related records from the table, and insert the two fields (ProductName, ProductPrice). How do I accomplish that with a calcuation instead of a portal?

[comment]

I am still not sure I follow this. Assuming you have a second relationship, a self-join of the VendorProduct table matching on vendor, and that the second TO of this table is named "VendorProduct 2", you can make the tooltip calculation =

List ( VendorProduct 2::ProductName )

This will show all the products that the vendor carries. If you also want to show their prices, you will need to define a calculation field in the VendorProduct table that concatenates ProductName and ProductPrice, and use this field instead of ProductName in the above formula.