Custom Virtual Tables

Neil HaddleyMay 7, 2024

Customer Content Virtual Tables

I created two related Business Central tables and connected to them as Power Platform Dataverse Virtual Tables.

The Car Brand table can be edited using the Car Brand List page

The Car Brand table can be edited using the Car Brand List page

The Car Model table can be edited using the Car Model List page

The Car Model table can be edited using the Car Model List page

The Car Brand and Car Model tables are shown in the Available Tables list

The Car Brand and Car Model tables are shown in the Available Tables list

The contents of the Car Brands table can be viewed using a Model Driven App View

The contents of the Car Brands table can be viewed using a Model Driven App View

The contents of the Car Models table can be viewed using a Model Driven App View

The contents of the Car Models table can be viewed using a Model Driven App View

A Car Model record can be updated using a Model Driven App Form

A Car Model record can be updated using a Model Driven App Form

Car.al

TEXT
1table 50200 "Car Brand"
2{
3    DataClassification = CustomerContent;
4    Caption = 'Car Brand';
5
6    fields
7    {
8        field(1; Name; Text[100])
9        {
10            Caption = 'Name';
11        }
12        field(2; Description; Text[100])
13        {
14            Caption = 'Description';
15        }
16        field(3; "Country"; Text[100])
17        {
18            Caption = 'Country';
19        }
20    }
21
22    keys
23    {
24        key(PK; Name)
25        {
26            Clustered = true;
27        }
28    }
29}
30table 50201 "Car Model"
31{
32    DataClassification = CustomerContent;
33    Caption = 'Car Model';
34
35    fields
36    {
37        field(1; Name; Text[100])
38        {
39            Caption = 'Name';
40        }
41        field(2; Description; Text[100])
42        {
43            Caption = 'Description';
44        }
45        field(3; "Brand Id"; Guid)
46        {
47            TableRelation = "Car Brand".SystemId;
48            // https://yzhums.com/22220/
49            ValidateTableRelation = false;
50            Caption = 'Brand Id';
51        }
52        field(4; Power; Integer)
53        {
54            Caption = 'Power (cc)';
55        }
56        field(5; "Fuel Type"; Enum "Fuel Type")
57        {
58            Caption = 'Fuel Type';
59        }
60    }
61
62    keys
63    {
64        key(PK; Name, "Brand Id")
65        {
66            Clustered = true;
67        }
68    }
69}
70enum 50200 "Fuel Type"
71{
72    Extensible = true;
73    value(0; Petrol)
74    {
75        Caption = 'Petrol';
76    }
77    value(1; Diesel)
78    {
79        Caption = 'Diesel';
80    }
81    value(2; Electric)
82    {
83        Caption = 'Electric';
84    }
85}
86page 50200 "API Car Brand"
87{
88    PageType = API;
89
90    APIVersion = 'v1.0';
91    APIPublisher = 'bctech';
92    APIGroup = 'demo';
93
94    EntityCaption = 'Car Brand';
95    EntitySetCaption = 'Car Brands';
96    EntityName = 'carBrand';
97    EntitySetName = 'carBrands';
98
99    ODataKeyFields = SystemId;
100    SourceTable = "Car Brand";
101
102    Extensible = false;
103    DelayedInsert = true;
104
105    layout
106    {
107        area(content)
108        {
109            repeater(Group)
110            {
111                field(id; Rec.SystemId)
112                {
113                    Caption = 'Id';
114                    Editable = false;
115                }
116
117                field(name; Rec.Name)
118                {
119                    Caption = 'Name';
120                }
121                field(description; Rec.Description)
122                {
123                    Caption = 'Description';
124                }
125                field(country; Rec.Country)
126                {
127                    Caption = 'Country';
128                }
129            }
130
131            part(carModels; "API Car Model")
132            {
133                Caption = 'Car Models';
134                Multiplicity = ZeroOrOne;
135                EntityName = 'carModel';
136                EntitySetName = 'carModels';
137                SubPageLink = "Brand Id" = Field(SystemId);
138            }
139        }
140    }
141}
142page 50201 "API Car Model"
143{
144    PageType = API;
145
146    APIVersion = 'v1.0';
147    APIPublisher = 'bctech';
148    APIGroup = 'demo';
149
150    EntityCaption = 'Car Model';
151    EntitySetCaption = 'Car Models';
152    EntityName = 'carModel';
153    EntitySetName = 'carModels';
154
155    ODataKeyFields = SystemId;
156    SourceTable = "Car Model";
157
158    Extensible = false;
159    DelayedInsert = true;
160
161    layout
162    {
163        area(content)
164        {
165            repeater(Group)
166            {
167                field(id; Rec.SystemId)
168                {
169                    Caption = 'Id';
170                    Editable = false;
171                }
172                field(name; Rec.Name)
173                {
174                    Caption = 'Name';
175                }
176                field(description; Rec.Description)
177                {
178                    Caption = 'Description';
179                }
180                field(brandId; Rec."Brand Id")
181                {
182                    Caption = 'Brand Id';
183                }
184                field(power; Rec.Power)
185                {
186                    Caption = 'Power';
187                }
188                field(fuelType; Rec."Fuel Type")
189                {
190                    Caption = 'Fuel Type';
191                }
192            }
193        }
194    }
195}
196permissionset 50200 "Car Brand"
197{
198    permissions =
199        tabledata "Car Brand" = RIMD,
200        page "API Car Brand" = X;
201
202}
203permissionset 50201 "Car Model"
204{
205    permissions =
206        tabledata "Car Model" = RIMD,
207        page "API Car Model" = X;
208
209}
210
211
212page 50210 "Car Brand List"
213{
214    PageType = List;
215    SourceTable = "Car Brand";
216    ApplicationArea = All;
217    UsageCategory = Lists;
218
219    layout
220    {
221        area(content)
222        {
223            repeater(Group)
224            {
225                field(id; Rec.SystemId)
226                {
227                    Caption = 'Id';
228                    Editable = false;
229                }
230
231                field(name; Rec.Name)
232                {
233                    Caption = 'Name';
234                }
235                field(description; Rec.Description)
236                {
237                    Caption = 'Description';
238                }
239                field(country; Rec.Country)
240                {
241                    Caption = 'Country';
242                }
243            }
244
245
246
247
248        }
249    }
250}
251page 50211 "Car Model List"
252{
253    PageType = List;
254    SourceTable = "Car Model";
255    ApplicationArea = All;
256    UsageCategory = Lists;
257
258    layout
259    {
260        area(content)
261        {
262            repeater(Group)
263            {
264                field(id; Rec.SystemId)
265                {
266                    Caption = 'Id';
267                    Editable = false;
268                }
269                field(name; Rec.Name)
270                {
271                    Caption = 'Name';
272                }
273                field(description; Rec.Description)
274                {
275                    Caption = 'Description';
276                }
277                field(brandId; Rec."Brand Id")
278                {
279                    Caption = 'Brand Id';
280                }
281                field(power; Rec.Power)
282                {
283                    Caption = 'Power';
284                }
285                field(fuelType; Rec."Fuel Type")
286                {
287                    Caption = 'Fuel Type';
288                }
289            }
290        }
291    }
292}