Diplay all records with conditional test

[georgewash]

I haven't found an answer to this one in the forums. BTW, any recommendatons on better reference books than the Missing Manual is appreciated (I hate having to ask questions all the time).

I want to diplay a list of records via a portal based on two fields from another table. Here are my tables:

Products:

kp_Product ID

kp_weekly Items (text field to choose "Yes" or "No"

kp_product name

Grocery List:

kf_Product ID

kp_Grocery List ID

Grocery List Generator:

kf_Grocery List ID

In the Grocery List Generator I want a portal to show all the product names that have been selected "Yes" as a weekly item.

I am not sure how to have a portal create a list of records. The closest answer I found was comment's GetNthInPortal example file, but I don't think that will work for me becuase the Products should be a child of the Grocery List Generator.

Second, I assume I need a Case function but am not sure where I would apply it.

Thanks.

[bcooney]

So, you're looking to have a layout that creates a grocery list from a library of products?

The tables are:

Products

GroceryList

GroceryListItems (this has fK_GroceryListID and fK_ProductID)

You can look to Invoice and InvoiceLineItem models as an example.

Adding Products to GrocListItems can be done a number of ways. You could simply have a popup menu in the GroceryListItems portal that is set to a value list of products. Or, you could build a New Product button that takes the user to a popup window of products in a list view or filtered portal.

You've labeled more than one field with the prefix kp. Since kp typically stands for "key primary," in Products only kp_ProductID would be necessary. I actually prefer "__kP_EntityID" because it sorts to the top of the field list.

Edited September 3, 2008 by Guest

[georgewash]

Thanks for the reply.

My issue is related to viewing specific products that have been tagged as 'weekly items' in the product table.

I did figure out how to get all of the products to populate the portal. I set the kp_Grocery List ID (Grocery List Generator table) and the kf_Grocery List ID (Product table) to 1. Then I created a portal using the Products table data and have edit boxes for the ID and name.

My issue is how to display only the names (IDs) that have been selected as "yes" in the Weekly Items field in the Product layout.

Obviously I need some type of conditional formating, but how to set it up is proving elusive to me.

[bcooney]

Argh!

Do you need to keep a history of Grocery Lists? If not, then you could simply mark a product as weekly* and find on that field and print the list. That would be your "Grocery List." You don't need any other tables.

Again, you never change _kP fields! They are auto-enter serial numbers.

Why not post a sample of your system and I'll help you to structure it.

*If this is destined to be a hosted, multi-user system, then we need to further explain how to mark a record in a multi-user friendly way.

[comment]

how to display only the names (IDs) that have been selected as "yes" in the Weekly Items field

Define an unstored calculation field (result is Text) =

"yes"

and define a relationship that matches this field to the Weekly Items field in the Products table.

BTW, it's better to use a number field for Boolean (true/false) values (1 for True, 0 or empty for False).

[georgewash]

Thank you so much for the offer.

The issue I have described is the latest one I've been attacking as part of a much bigger database. I would not want you to have to spend the time going through my mess to de-bug.

If you are really up for the challenge, I'll post what I have after I figure out a few more things and tell you what I am trying to achieve overall and where I am stuck.

[georgewash]

Ok, I have figured out how to get portals to list all records thanks to your example. Pretty slick, I would have thought there would be some recursive issue.

My portal shows all the Product IDs, Product Names, and Yes or No (from the weekly field). I created a field in the Grocery List Generator table called kf_weekly item that is a calculation = "Yes" with text as the result. I joined it to the kp_weekly item field in the Products table.

So now I'm stumped as how to get the portal to just show the records with "Yes".

Thank you for all the help, I am truly humbled and grateful.

[comment]

I am a little confused by your description. In general, to filter a relationship you need to add some criteria ("predicates", to use the correct technical term) to the relationship's definition. For example, this:

Parent::ParentID = Child::ParentID

will show all childen of a parent record, while this:

Parent::ParentID = Child::ParentID

AND

Parent::cTrue = Child::Status

will show only those children of a parent whose Status is true (Parent::cTrue could be a calculation field defined as = 1).

[georgewash]

Cool. That helped. I set the kp_Grocery List ID = 1 and the kf_Grocery List ID = if (kp_weekly item = "Yes";1;2). So now only those items show up in the Grocery List Generator layout. I'm not sure if this is exactly what you had in mind, but it seems to be working. I'm a little concern how I am going to get all more portals to create one unified list, but that is still a bit off.

Thanks again.

[comment]

That should work, except the calc could be simply:

kp_weekly item = "Yes

This returns 1 when true, 0 (or empty) otherwise. It would be even simpler to change the kp_weekly item field to a Number field, as mentioned before - than you wouldn't the calc field at all. See also:

http://fmforums.com/forum/showtopic.php?tid/181943/

A note about your field names: while I hate all these kp_ and kf_ prefixes, if you're going to use them at least use them right. A calculation with a constant result of 1 is not a primary key or an ID, by any means. A primary key is a field that uniquely identifies a record, such as a serial-numbered ID. Similarly, the calculation field is not a foreign key, even if it's used in defining a relationship. A foreign key is the field populated with a value matching the parent's table primary key.

[georgewash]

Ok, next problem.

On my layout I also want to inclue a portal for Inventory items. I added the portal and everything is fine, except I cannot limit the products shown. I want to only see products that are below a specified re-order limit.

I tried the same technique as with the weekly items, but I cannot the kf_Grocery ID in the Inventory table with the kp_Grocery ID of the Grocery List Generator table since that is already linked to the Products table kf_Grocery ID and the Inventory table kf_product name is linked to the Product table kp_product name.

Product:kp_product name:Inventory:kf_product name

Product:kf_Grocery ID:Grocery List Generator:kp_GroceryID

I'm not sure how I am getting any records in the Inventory portal right now. It's not the complete list and I can't figure out what it is filtering.

Maybe there is a better way to set this all up.

[comment]

I am not sure I follow this (as I said, your names are confusing), but it sounds like you need to put more occurrences of some tables on the relationship graph.

To get back to my previous example: if I wanted the Parent record to show BOTH a portal with ALL its children AND a portal with only SOME of its children, I would need two occurrences of the Child table, and two relationships (one to each TO of the Child table) one "plain", and one filtered.

[georgewash]

Ok. I made a second TO of the Inventory table and created a relationship between Grocery List Generator:kp_Grocery List ID and Inventory2:kf_Grocery List ID.

Now I get no data in the portal.

I am not very clear on parent/child relationship terminology. I am still getting my head wrapped around join tables.

Any links to a good explanation of how these work (especially with a second TO)?

When you make a duplicate TO do the fields automatically pull the records from the original TO?

Thanks.

[comment]

I am not very clear on parent/child relationship terminology.

Parent/child are convenient generic terms to describe a simple one-to-many relationship - parent is the one, child the many (i.e. the records you see in a portal).

Now I get no data in the portal.

It's difficult to debug without seeing the file. Doubly so when you insist on using misleading names. Perhaps the attached example can help.

Groceries.fp7.zip

[georgewash]

Thanks, I'll take a look at it.

Sorry about the incorrect labeling of fields. I will attempt to follow your convention, I didn't know there was much besides primary and foreign key designations.

[georgewash]

I'm not sure what it was in your example, but I was able to get it to work. I thought I had already tried what ended up working.

Thanks again.