Major portal Relationship issue

[hamelekim]

I have a database that is displaying records that aren't related to the current record of the layout in a portal.

I will explain the two tables that matter first.

I have a BOM Table, and an Item Master Table.

There are three types of items in the Item master table. Formula, Item, and Packaging. Each of these has the same information but are not actually labeled as being any of those three types. I have had to use a calculated field to check the first two digits to label them one of the three types.

Now, there is a second table BOM that has Item Numbers and then the Formula and Packaging numbers that go along with that Item number.

So you could have Item number

1 with packaging ID 34

1 with packaging ID 56

1 with formula ID 23

1 with formula ID 13

2 with formula ID 57

2 etc....

2

2

The first column with 1 and 2 would be the field Item Number

The second number would be the Component field.

so you have multiple Item Number with the same ID in one table, and that's the Primary KEY!!! Not my database design btw, it's the companies.

So they want the packaging and formula information split up into two different portal. This is where my calculated field from Item master table comes in. I have three calculated fields in the BOM Table that are linked to this Forumla Item calculated field. One has "Packaging", the other "Formula", The third is "Item".

Doing this allows me to set up portal for each of these item types so I can display only Packaging, or only Formula and still get the Details that are only in the Item Master table for each component id number.

Now comes my problem. I am getting component numbers and details showing up in my Formula and Packaging portals that don't actually exist in the BOM Table.

So say I have the following in the BOM table

Item component

1 - 34

1 - 56

1 - 23

1 - 89

but in my portal I will have

1 - 34

1 - 56

1 - 23

1 - 89

1 - 67 This shouldn't be here but it is

So I have an item showing up that isn't actually in my database. There is no Item number 1 with a component number of 67, but it shows up. Now, when I do a search for 67 in the BOM table under the components field the record 1 - 67 will show up if I am currently viewing the ID 1. But if I go to ID 2 and then do a search for 67 the record 1 - 67 doesn't show up. I am really completely lost as to what could be the problem. I mean I know the database is messed up from the start in terms of the way it was designed, but i am stuck with it. The other issue is that the program is completely done except for this issue, and this issue makes the entire database useless.... thousands of dollars down the drain. needless to say I am trying to find out what could possible be wrong. I have tried exporting data in to a new table and starting from scratch with the relationships, I still get the same result.

Has anyone ever had this same sort of issue before?

[Vaughan]

"..so you have multiple Item Number with the same ID in one table, and that's the Primary KEY!"

I stopped reading about this point, but I was already wondering if the problem was key-related.

Unless the relationship is fixed up and primary keys are sorted out you'll always have problems.

[LaRetta]

Another possibility:

Have you double-checked the field-specified table occurrence compared to the portal-specified occurrence? If you have a mismatch (incorrectly specified field) it might display this type of inconsistency. And it may not be OBVIOUS because the Item Numbers are usually the same. Or maybe that calc isn't refreshing - sometimes they need help depending upon their chain of evaluations.

It would probably help to see those calcs and how the relationship is established because that can most certainly cause problems as well.

LaRetta

[hamelekim]

Heh. Wonderful. Yeah, the database they have that I am pulling everything out of is just crap. Is it possible to attach images to my posts? I will post a few for reference.

[hamelekim]

I think I might have solved it. I just added another table where I imported every single item number from the Item Master table. Any Item number located in the BOM table is also in the Item Master but only once. So I have

Baan Table********************BOM******************ItemMaster

Item Number -----------Item Number--------------Item No

************************** Component Number--------Item No

********************************************************* Descr

I had to do all of that just to get the description for each item and component number. I just need to split up the Formula and Packaging items in the BOM table now. Hopefully this works. I should have done this from the start instead of fooling around with a broken database.

Ah, that didn't show up very well.

Edited March 17, 2006 by Guest

[hamelekim]

After doing all that I am getting an index not found when I try to put a related field on the form... What could be causing this?

[hamelekim]

That is my relationship. The linked fields are all indexed and have data in them. But I still get when I put a field from Item Master Baan Connection 2 in a layout with Baan as the table. The description from the Item master table hsould be displayed that matches with the Item Trimmed number....

[hamelekim]

Here is the field on the form

[hamelekim]

Ah, nm. I figured it out. It was a calculated field and I had to set it to store the result and index all.