2

I've been thrown quite the scenario today. Essentially, I have one table (ProjTransPosting) that houses records, and that table relates to a number of similarly structured tables (ProjCostTrans, ProjRevenueTrans, etc). They relate by TransId, but each TransId will relate to only one of the number of child tables (meaning if a TransId of 137 exists in ProjCostTrans, there cannot be a TransId of 137 in ProjRevenueTrans). The schemas of the children tables are identical.

So, my original thought was to create a Map and create the mappings from the various children tables. And then I would use this Map as a datasource in the form so everything can show up in one column. I created all the relationships between the Map and the children table along with the relation to the parent table. I put Map in the form as a datasource and this caused a blank Grid, although I don't know why. Is it the case that the Map object can only by of one table type at any given time? I thought the purpose of this was that it could be universal and act as a buffer to many record types. I'd like to pursue this route as this definitely would achieve what I'm looking for.

In failing this I was forced to arrange my Data Source to perform something like this: SELECT ProjTransPosting LEFT JOIN ProjCostTrans LEFT JOIN ProjRevenueTrans ... The problem with this is, each child table I add-on, it's creating additional columns, and the values of the other columns are all NULL (blank in AX). So I have something like this:

Parent.TransId   ChildA.Field   ChildB.Field   ChildC.Field
1                NULL           1256           NULL
2                1395           NULL           NULL
3                NULL           4762           NULL
4                NULL           NULL           1256

Normally, the user would deal with the annoyance of having the extra columns show up, but they want to also be able to filter on the fields in all the children tables. My example above, they want to be able to filter "1256" and the results would return TransIds 1 and 4, but obviously since the values in this case are spread out in multiple columns, this cannot be done by the user.

Ideally the Map would "combine" these columns into one and then the user could filter easily on it. Any ideas on how to proceed with this?

codemann8
  • 372
  • 7
  • 29

2 Answers2

4

Try creating a union query and then a view based on that query.

Maps are supposed to be used only in X++, and not as data sources in forms.

10p
  • 5,488
  • 22
  • 30
1

This sounds like the exact purpose of table inheritance in AX 2012.

http://msdn.microsoft.com/en-us/library/gg881053.aspx

When to use:

http://msdn.microsoft.com/en-us/library/gg843731.aspx

EDIT: Adding my comments here to make this a more full answer.

Let's say you have three tables TabPet, TabPetCat, TabPetDog, where TabPet is the supertype table and the others are decedents.

If you insert two records each into TabPetCat and TabPetDog (4 total), they will all have unique recIds. Let's say TabPetCat gets 5637144580 and 5637144581. TabPetDog gets 5637144582, and 5637144583.

If you open TabPet, you will see 5637144580, 5637144581, 5637144582, and 5637144583.

So what you would do is make your table ProjTransPosting the supertype and then ProjCostTrans, ProjRevenueTrans, etc descendant tables. Unless transId is really necessary, you could just get rid of it and only use RecId.

Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71
  • Except that there is no RecId linkage between the tables, just this TransId. – codemann8 Dec 04 '14 at 14:06
  • Yes, but you said yourself that only 1 `TransId` exists at once in any table. So use `RecId` and leave the `TransId` in the parent table. Are you tasked with making this change to existing tables/schema or are you in control of the development of it? – Alex Kwitny Dec 04 '14 at 17:47
  • This still wouldn't work. A RefRecId is only possible if it relates to exactly one table. Two different tables can have the same RecId, it wouldn't know which table is the correct one. – codemann8 Dec 08 '14 at 20:36
  • I don't think you fully understand how table inheritance with AX 2012 works. Let's say you have three tables `TabPet`, `TabPetCat`, `TabPetDog`, where `TabPet` is the supertype table and the others are decedents. If you insert two records each into `TabPetCat` and `TabPetDog` (4 total), they will all have unique recIds. Let's say `TabPetCat` gets 5637144580 and 5637144581. `TabPetDog` gets 5637144582, and 5637144583. If you open `TabPet`, you will see 5637144580, 5637144581, 5637144582, and 5637144583. – Alex Kwitny Dec 08 '14 at 22:07
  • So what you would do is make your table `ProjTransPosting` the supertype and then `ProjCostTrans`, `ProjRevenueTrans`, etc descendant tables. Unless `transId` is really necessary, you could just get rid of it and only use `RecId`. I'm editing my answer with these comments just so you can see formatting easier. – Alex Kwitny Dec 08 '14 at 22:10
  • I see now, I agree now that could work had it'd been done like that from scratch, however, existing historical data currently now has colliding RecId's. I'm pretty sure multiple tables already reference their RecId's, so they cannot change. This would be problematic to try to implement table inheritance for existing data. – codemann8 Dec 09 '14 at 23:29
  • The data would be easy...the existing code/logic that uses those tables would be difficult. For the data, you'd just do several `insert_recordset` commands for the `descendant` tables. But finding every place that posts to those tables plus reports that use the data, etc...that's whatever you think it is. It's too bad though because I rarely can think of a good reason to use table inheritance in my day-to-day development. Good luck! – Alex Kwitny Dec 10 '14 at 01:18