Get portal to show several possible matches

[mike60640]

Hello,

Sometimes when I'm planning a database my head starts spinning and I get stuck. Can anyone help me think?

I have a Product table and each product has a client name and 3 different product numbers (each means something different). I then have a Receiving-Line-Item table to check in products. Products coming in may have one of the numbers and not the others. When entering in the Receiving-Line-Item table I would love to have a portal showing matching existing data in the Product table. When entering the client, then the number of items in the portal would go down and only show those clients. Then entering ONE of the other numbers would show even fewer choices in the portal.

Obviously the single portal can't show "this relationship" AND "this relationship" AND "this one". What fields to add or calculations to do. I'm stuck. Is this possible? Any thoughts?

[mike60640]

Although I think it would be nice to give the person a pick-list of existing products to pick from that match... It may let them confirm the other details of the product before confirming it is the same item or it's a new item to be added to the Product table.

But if I can't do this successfully, maybe another way to enter the existing matching data is using look-up tables but this is not prefered

[comment]

3 different product numbers

Can you elaborate on this? In particular, is it possible to have a collision between the numbers - i.e. one product's number A is identical to another product's number B?

Products coming in may have one of the numbers and not the others.

When you receive a product, do you know which number type that one number is?

[mike60640]

Can you elaborate on this? In particular, is it possible to have a collision between the numbers - i.e. one product's number A is identical to another product's number B?

When you receive a product, do you know which number type that one number is?

Comment,

The numbers (actually need to be text fields) may theoretically be generated and be the same, but that is statistically unlikely to happen. They are all generated separately for different purposes and then for us to get that particular product is a long shot, but could happen I guess.

Yes, when we get a number we do know which number it represents.

[comment]

OK, then let's take a simple one to begin with. I need some names here, so I'll call the three product numbers ProdNumA, ProdNumB and ProdNumC. In the Products table, define a calculation field cProdNums (result is Text) =

List ( ProdNumA ; ProdNumB ; ProdNumC )

Note: if you're still using version 7 as your profile shows, you'll need to use:

ProdNumA & ¶ & ProdNumB & ¶ & ProdNumC

Define a relationship between LineItems and a TO of Products matching the incoming product's number to cProdNums.





The next step is to incorporate the client's name:

In the Products table, define a calculation field cClientName =

List ( ClientName ; "All" )

In the LineItems table, define a calculation field cClientName =

Case ( IsEmpty ( ClientName ) ; "All" ; ClientName )

Add a match of these two fields to the above relationship.

[mike60640]

Thanks Comment. I have FM v.11 Advanced now. I've been away from FM for the versions in between.

This helps but it doesn't work the way I need.

example entry...

Product 1A data:

ClientName = Sears

ProdNumA = 001

ProdNumB = AAA

ProdNumC = 11AA1

Right now when entering ClientName the portal come up empty, but would rather it show me All the Sears products. Then enter ProdNumA and will show Product 1A. When entering ProdNumB and ProdNumC data doesn't efect the portal at all, either with ClientName or ProdNumA data present or not present.

But, right now if I enter JUST the ProdNumA it shows All products regardless of client (this is good).

I guess I need to consider all 4 of these fields as equal and when entering one of them the portal shows matching, then when entering another one the portal will narow down the portal results. Does that help?

I really appreciate your help.

[mike60640]

I think the problem comes from the relationship. I have ClientName, ProdNumA, ProdNumB and ProdNumC in both tables. And when you recommended making a relationship to the new cProdNum field in Products, then to which of those ProdNum fields do I relate? Right off the bat I did ProdNumA. That's why it appeared to be the only one working. Should I then make a cProdNums field in LineItem table to use in the relation?

I did this and AFTER entering ClientName it works. The portal shows the products regardless of which ProdNum field I use to enter another ProdNum data into. This might be ok. I need to discuss with my client all the clients possible data.

But what if I wanted to ONLY show results when entering the ProdNumA into ProdNumA field? Like I said before... when entering one ProdNum (A, B or C) into it's correct field them the portal shows matching records, then when entering another field the portal will narrow down the portal results.

[comment]

Right now when entering ClientName the portal come up empty, but would rather it show me All the Sears products.

That's entirely possible, using the same principle as with the client: make the cProdNums calculation in Products =

List ( ProdNumA ; ProdNumB ; ProdNumC ; "All" )

and add a second calculation field to the LineItems table =

Case ( IsEmpty ( ProductNumber ) ; "All" ; ProductNumber )

Change the first row of the relationship to match these two fields. Now, when both fields are empty you should see all products*, and any entry - in any order - will narrow the choices.

---

(*) This can be changed, if you prefer, by adding a condition to the Case() statements in LineItems.

I have ClientName, ProdNumA, ProdNumB and ProdNumC in both tables.

Is this necessary? I mean in the LineItems you could do with one field only. Otherwise the calculations need to be modified slightly.

[mike60640]

Is this necessary? I mean in the LineItems you could do with one field only. Otherwise the calculations need to be modified slightly.

Yes, I think all are needed. The idea is they are entering all the data for a incoming product. At any time I want them to see matching data in existing Product table, and if they see something matching they can pick it to auto complete the data entry and that will also assign that item the same [hidden] serial number.

Clients send in merch with one or two of these numbers but never all of them at the same time, hence the more complex relationship to view existing records.

[comment]

At any time I want them to see matching data in existing Product table, and if they see something matching they can pick it to auto complete the data entry

IMHO, the data entry doesn't need to be completed. In fact, I would enter the data into global fields and create a record only after they select the product from the portal. There is no need for the line item to replicate the data in Products.

[mike60640]

There are more layers to this DB than I have not divulged. The Line Item info needs to be retained because some samples may come in damaged while others do not. The Product info that I'm pulling details for are specific to that product, but then that particular sample will then be called upon to be shipped back, while other samples (of that same product) may stay around.

[comment]

Sorry, I don't follow you. As you said, eventually the line item is populated with the "real" unique ProductID. From that point on, all three product numbers and the client are available directly from the Products table through the relationship matching on ProductID.

[mike60640]

You are right, the Line item doesn't need to replicate data already in the Product table. I was thinking of the next step, when this is a NEW product (no existing product was selected) and that data will be needed.

I guess I can use some global fields in LineItem table for data entry, If no matching portal products were selected then will create a new Product and create a Line Item w/the same unique ProductID.