Hello, I'm having an issue with a portal that I'm using in my one database. What is happening is I have a portal that is used to display products from another table (invoicing database). The portal pulls all the data fields just fine (e.g. Prod name, color, weight, etc.) when using the PRODUCT SERIAL NUMBER (FMP generated) as the select/trigger field. What I need to happen is all this data to be pulled from the products table using another field instead, I want to use the PRODUCT ID field (not FMP generated) to pull all the products data field information (e.g. Prod name, color, weight, etc.). As of now this is not functioning and I can't seem to figure it out. I think it may be in the relations, but not sure.

ANY helpful advise would be GREATLY appreciated!!

Thank you

David

If you need more information to figure this out please let me know.

This is a standard setup. Do you have all of these tables?

+--------+     +----------+     +-----------+     +-----------+

|Customer|     |Invoice   |     |LineItem   |     |Product    |

|--------|     |-------   |     |--------   |     |-------    |

|ID_cust |-----|ID_cust   |     |           |     |           |

|        |     |ID_invoice|-----|ID_invoice |     |           |

|        |     |          |     |ID_product |-----|ID_product |

+--------+     +----------+     +-----------+     +-----------+

-jens

Hello, thanks for getting back to me . Yes, I have the standard setup of tables (Products, Line items, Customers, Quotes, Invoices).

Can you tell me whether or not this is a relations problem? How I can make the Prod ID (non FMP serialized) field the trigger field to pull all the related data from that related product into the portal?

All the tables are related using Primary and Foreign keys.

Thanks

Dave

Edited June 4, 200718 yr by Guest

If you already have an relationship via your fm generated key, you might need an additional TO and relationship via the non fm generated key. Does a field exist on both sides of the relationship with this field?

-jens

Hmm, well I do have a field (related field) on the two tables that I'm trying to work between. I have tried this a couple different ways and I think I have tried using an additional TO.

I have an ID field in the Products table. I have also tried using an ID field in the Lines table (what the portal uses for it's info) with a lookup to the ID field in the Products table.

Seems as though this should be fairly straight forward to accomplish.

I really need to finish this part of the database so any help you can give would be GREATLY appreciated!

Thanks

Dave

I want to use the PRODUCT ID field (not FMP generated) to pull all the products data field information (e.g. Prod name, color, weight, etc.)

This is where you're in trouble, you need a lookup to get the internal linking ID's value, into the join table ...since linking on something not internally generated is flaky to say the least if it suddently gets a new convention code, plus noramlization seeks to avoid more than one fact per field - your product code is such a concatanation.

This means you need an extra TO of the Product table, with a field for each category - split by 3-4 calcfields, and a multi criteria lookup ...but straight forward oldfasion lookups will only change value, when a match exists ...this means if you change your mind during the typing and have entered something before that accidentally produced a match, will the field keep this wrong value if no new value is entered.

The solution is to use Autoenter calc's that replaces existing value ...because as soon you tamper with a criteria and you enter something not matching will the field be cleared - I hope you can see this helps to prevent illegal or non existing product codes?

--sd

Hello, and thanks for your reply. I might be a little confused with your reply. Are you saying I need to do the Autoenter calc with another TO of the Products or just the Autoenter calc ?

How would I get the calc to take the input from the field and find the correct corresponding record and display it's information?

Thanks

Dave

Are you saying I need to do the Autoenter calc with another TO of the Products

Yes according to the rule: You should (Thou shalt) facilitate your solution with enough TO's...

Here does it mean 3 calc'fields looking on each it's own part of your intered product code in the join table to interpret it. These 3 fields are primary keys for a multicriteria relation matching a corresponding set in the products table. Take a look at the attached template to see how to set up the new TO....

--sd

MCLookup.zip

Edited June 4, 200718 yr by Guest
added a template

Soren, Thank for the demo file. I think I see what is going on here, but I can't seem to make any difference in your demo using thelookup field, am I missing something?

I'm trying to see how I can take this info and utilize it in my db to call the info I'm trying to retrieve.

Thanks

Dave

P.S. When you refer to "TO" you are referring to a table occurrence correct?

Edited June 4, 200718 yr by Guest

but I can't seem to make any difference in your demo using thelookup field, am I missing something?

...theLookup, is what it is not something for you to use - It's meant to be the value you need for the internal relational linking, would you ever give users access to your key values??

It's the one with the pipes that gives the combination to look for. In your solution could you build your product code sections with a leading or several zeros, instead of the pipes, so the calc's knows that say char 1...3 is colour, 4...6 is size etc.

Yes TO is table occurance, TOG is table occurance group...

--sd