Filtering by another field without using a portal

[JudyQ]

This is probably simple solution and I'm overlooking something...   I'm creating an estimate solution with all of the "Items" in first table and the second table holds each estimate for the client.  ie.  there is a long list of "items" that would be easier to choose from if the list wasn't so long.  Trying to constrict/filter the list by category.

Two tables:  Items & Estimates

Two fields:  Category and Item in both

Need the list to be filtered by the category.  I do not want to use a portal because the "Item" should be housed in the estimate.  I've tried self-joining relationships but I seem to be missing something or they are only portal based?

[comment]

I am not sure I understand your description.

Normally, in a solution where an estimate can have many items, and an item can appear in many estimates (i.e. a "many-to-many" relationship between Estimates and Items), there would be three tables:  Estimates, Items and a join table in-between them (commonly called a LineItems table). The items selected to be included in an estimate would not be "housed in the estimate", but listed as individual records in the LineItems table and shown in a portal on the layout of Estimates.

This arrangement allows each line item to have it own attributes (such as price and quantity), independently of other occurrences of the same item in other estimates. 

But perhaps I am missing something and your situation is different (your post seems to suggest that every estimate will have at most one item). Let's clarify this point before discussing how to filter items by category.

 

Edited February 26, 2024 by comment

[Søren Dyhr]

14 hours ago, JudyQ said:

Need the list to be filtered by the category.  I do not want to use a portal because the "Item" should be housed in the estimate.  I've tried self-joining relationships but I seem to be missing something or they are only portal based?

I would like to see the graph of the relational aspects of the matter, each field should seemingly only belong to one of the tables???

--sd

[JudyQ]

Perhaps I need to change the model?

I would have preferred to have a "Material" AND "Equipment" table and pull the "Labor" from the Employee file.  As it is I've put all three together in one table.

However, in the case I have here It would be nice to select the Category and the Item List constrict to only the correct category.  I'm sure there are more elegant solutions, but this one works fine for now except for the long list I get in the Items field.

Thanks for any help. J

[comment]

1 hour ago, JudyQ said:

Perhaps I need to change the model?

If you want an estimate to include more than one item, then yes - you most certainly need to change your data model.

 

1 hour ago, JudyQ said:

I'm sure there are more elegant solutions, but this one works fine

I am afraid I don't see how it can work "fine" - unless your estimate will never include more than one item.
(Or is it possible that your Estimate Entry::Item Name field is a repeating field? That is not a good arrangement to have!)

I see no point in going further before we clarify this issue, since the solution will be different for each case.

 

Edited February 26, 2024 by comment

[Søren Dyhr]

2 hours ago, JudyQ said:

Perhaps I need to change the model?

Comment have already given you some healthy pointers, but there is another matter, of some concern ... never use human entered fields as link, a tiny typo - breaks the linking immediately and I agree that the repeating field is a bad substitute for a dedicated join table.

 

21 hours ago, JudyQ said:

I do not want to use a portal because the "Item" should be housed in the estimate.

That's not a genuine concern, values can be ushered in actually from several realtional links away!

--sd

[comment]

FWIW, here's a basic demo file showing the standard data structure for Invoices/Orders/Estimates, with the added ability to select a product from the selected category.

A few points to take note of:

• All core relationships (the top row of the RG) use IDs as match fields; this allows you to change names without breaking existing relationships;
• The product name is displayed directly from the Products table, but the price is looked up into the LineItems table so it can be modified without affecting other invoices.

With regard to selecting the product:

• This is a "no frills" demo;  in the actual implementation you may want to add some features - for example, prevent selecting another category without also selecting a new product;
• There are other methods of selection, such as selecting from a portal (using the same auxiliary relationship as the one in the file) or from a card window (using a Find to show only products in the selected category).

 

 

InvoicesBasic+Select.fmp12

Edited February 26, 2024 by comment

[Søren Dyhr]

1 hour ago, comment said:

This is a "no frills" demo;  in the actual implementation you may want to add some features - for example, prevent selecting another category without also selecting a new product;

There is unfortunately a tiny flaw, it shouldn't in my humble opinion - be possible to enter a stray number without presence in the particular list, the routined user skips the selection with the valuelist ... i managed to select Dairy and following it with a productID of 4 - although Cherries hardly belong to this category!

Member of a dynamic valuelist - should be evaluated!!!

--sd

Edited February 26, 2024 by Søren Dyhr

[comment]

1 hour ago, comment said:

This is a "no frills" demo;  in the actual implementation you may want to add some features

It has always been my policy, when posting an example file, to keep it to the absolute minimum required to demonstrate the issue at hand. Thus people can concentrate on the solution to their problem, without being distracted by all kinds of things that fall in the "nice to have" category.

 

Edited February 26, 2024 by comment