Trace:
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision Next revision Both sides next revision | ||
visionx:masterdetail_nofk [2020/03/09 09:27] admin created |
visionx:masterdetail_nofk [2020/03/09 11:27] admin |
||
---|---|---|---|
Line 1: | Line 1: | ||
~~NOTRANS~~ | ~~NOTRANS~~ | ||
- | ~~Title: Use Master/Detail relation without Foreign Key in Database ~~ | + | ~~Title: Use Master/Detail without Foreign Key ~~ |
+ | Usually, VisionX creates Master/Detail relations with Foreign Keys directly in the database, e.g. | ||
+ | {{:visionx:masterdetail_fk.png?nolink|}} | ||
+ | |||
+ | This is the perfect use-case for a Master/Detail screen like this one: | ||
+ | |||
+ | {{:visionx:masterdetail_screen.png?nolink|}} | ||
+ | |||
+ | Every Project (1) has a list of tasks (2). This is the perfect scenario. | ||
+ | \\ | ||
+ | \\ | ||
+ | But if you have an existing database schema, it's also possible that the model is not in [[https://en.wikipedia.org/wiki/Database_normalization|Satisfying 3NF]]. The model might look like this one: | ||
+ | |||
+ | {{:visionx:masterdetail_nofk.png?nolink|}} | ||
+ | |||
+ | The foreign key is missing in the database. But no worries, it's still no problem to create a screen with a Master/Detail relation as shown above. Best would be, to create a new empty screen (1): | ||
+ | |||
+ | {{:visionx:masterdetail_newempty.png?nolink|}} | ||
+ | |||
+ | Next, let us include the table objects. Start by creating a new table (2). The new table wizard will appear: | ||
+ | |||
+ | {{:visionx:masterdetail_newtable.png?nolink|}} | ||
+ | |||
+ | Please choose **Use existing data from database tables** (1) | ||
+ | |||
+ | {{:visionx:masterdetail_newtable_appuser.png?nolink|}} | ||
+ | |||
+ | and **Use Application Database User** (1). In the next step, choose the master table: | ||
+ | |||
+ | {{:visionx:masterdetail_projects.png?nolink|}} | ||
+ | |||
+ | In our example the master table is **Projects** (1). Now we need the detail table as well. Repeat last 4 steps and create a new table for the Tasks table. The result should look like: | ||
+ | |||
+ | {{:visionx:masterdetail_tables.png?nolink|}} | ||
+ | |||
+ | The data area contains the Projects table (1) and the Tasks table (2). Now we need to create a relation between the Projects and Tasks table. To achieve this: | ||
+ | |||
+ | {{:visionx:masterdetail_edit_tasks.png?nolink|}} | ||
+ | |||
+ | edit the Tasks table (1) and Show more (2) options. Next, enable Database changes (1) and Navigate to the Projects Id column (2). Press Define Combobox (3) | ||
+ | |||
+ | {{:visionx:masterdetail_edit_tasks_dbchanges.png?nolink|}} | ||
+ | |||
+ | and use the existing Projects table (1): | ||
+ | |||
+ | {{:visionx:masterdetail_edit_tasks_projects.png?nolink|}} | ||
+ | |||
+ | Now we have a relation between Projects (ID) and Tasks (PROJECTS_ID). Be sure that you press Finish. No worries, the database won't be changed in this case.\\ | ||
+ | \\ | ||
+ | As final step, we have to define the master table reference. Start the **edit Tasks table** wizard once again and set the Master Table to Projects (1): | ||
+ | |||
+ | {{:visionx:masterdetail_tasks_master.png?nolink|}} | ||
+ | |||
+ | The data area for the Tasks table now has a different symbol (1) which means that the Tasks table has a master: | ||
+ | |||
+ | {{:visionx:masterdetail_tasks_icon.png?nolink|}} | ||
+ | |||
+ | Now it's possible to use the tables as usual and the Master/Detail will work automatically. |