This topic is locked

Add and Edit page for details records

7/9/2011 6:12:58 PM
PHPRunner General questions
B
btrdev author

I have a master-detail relationship between two tables, and I need to present this to the user as on one screeen when viewing, adding or editing records. I have checked the "Display child records on" for the "View" and "Edit" pages. (I know from reading some posts in this forum that there is an issue with checking it on the "Add" page).
However, when I display an Edit page for the master record, the only options available for adding or editing the detail records are the "Inline Add" and "Inline Edit". The options for going to the "Add" and "Edit" pages for the detail records are not available. However, these options are available when I go directly to the details table.
How do I get the links for the "Add" and "Edit" pages for the detail records in this scenario? In this particular case, I want to disable the "Inline Add" and "Inline Edit" options for the detail records, since they don't really do what I want.
Any suggestions would be appreciated.

C
cgphp 7/10/2011

I think that this is a by design feature.

E
electromotive 7/10/2011



I have a master-detail relationship between two tables, and I need to present this to the user as on one screeen when viewing, adding or editing records. I have checked the "Display child records on" for the "View" and "Edit" pages. (I know from reading some posts in this forum that there is an issue with checking it on the "Add" page).
However, when I display an Edit page for the master record, the only options available for adding or editing the detail records are the "Inline Add" and "Inline Edit". The options for going to the "Add" and "Edit" pages for the detail records are not available. However, these options are available when I go directly to the details table.
How do I get the links for the "Add" and "Edit" pages for the detail records in this scenario? In this particular case, I want to disable the "Inline Add" and "Inline Edit" options for the detail records, since they don't really do what I want.
Any suggestions would be appreciated.


For edit/update it might be easier to use the normal master-detail mechanism to add/update the details if you can find a way not to confuse or annoy the user by jumping around between a pile of different forms which are basically manipulating the same data. Add Master, Back to List, Find the master record, Click on Detail Link, Add Detail. Then once you are here you can stay adding more detail records. You can also automate this to some extent by adding an event to jump directly to the detail add form once the master record has been created, saving on some clicks.
However, I have done the writeable multi-table views and once you get your head around it, it ends up being very simple to implement, a few lines of SQL and PHP. The tradeoff is making the designer work verus making the user work.
I frequently need this feature - Where there is one form to add/edit a master-detail pair/triplet, and where the user doesn't see or know about the distinction between master and details. It's all just one blob of data to them. But I wouldn't use this for a purchase order or an itemized bill of goods where there is a clear logical distinction between header and rows - you'd use inline. But there are lots of cases where the data the user wants to see is physically scattered across multiple tables - what the user wants to see is called a logical view.
For example on why you might need this - lets say you have a database of all animals, classifications of the animal kingdom. There are typically 7 levels, orders and families, etc. Now we have a department that deals only with cats. When reading/writing cat data, they can use a narrow view, a specialization, maybe just administer the bottom few levels - Carnivores and below. All carnivores have many characteristics in common including cats. So even though the view is about cats, they are still entering information about carnivores. The forms they use can display data from all levels, maybe allow them to cross match all animals living within a certain district, who is competing for food, who is eating who, etc.
The fact that there is a master-detail "normalization" thing happening is a technical issue regarding the design and administration of the underlying database. In many cases the users should not have to be exposed to this - they need a logical view, and there are usually a myriad of views needed from the same dataset. In fact the DB designer should be able to come along and completely change the underlying table structures and the user need not have to be aware.
Unfortunately we appear to frozen in time and the underlying SQL-type database engines haven't been evolving. They inherently support read-only multi-table views, but not writeable multi-table views. To get around this current limitation, you have to programmatically do the adds/updates, if you want to add/edit data into multiple tables from one form with one click. There are other types of datasets which don't use columns and rows and joins, but that's beyond discussion here.
What we are trying to avoid by using views is two things:

  1. Requiring the ordinary user to become aware of underlying DB structures
  2. Requiring the user to take two or more steps to do what could be done in one step
    This works best when there is at least a 1:1 relationship between master-detail, in other words when you always create a detail record when you create a master. It doesn't make sense when there can be 1:0 relationships, in other words master records without any details, or 0:1 relationships, or details records without any master.
    There are several parts to doing this using PHPR. It's a bit complicated but it works. With a good technical grasp it requires a few hours to design, code and test. You need a good reason to do it. Once you get the framework, then you can reuse. The whole idea is to make using the system simpler for the average user. Here is just one type of solution. But there are others.
    First of all you need to build the forms, and for PHPR to create the form code, it needs to work off a table or view. There are two ways to do this, create the multi-table view in the database or create the view within PHPR. I have experimented with both approaches, at the moment leaning toward database views. Then you can use foreign keys more readily. Generally you might expect these tables to be in the middle of a network of tables, with superior and inferior linkages. Would be nice if all this worked like ordinary tables.
    The view needs to have a unique key, otherwise you can't do updates/edits. So setting it up is counter-intuitive, in that the detail becomes the "master". The detail record key is unique. The master record key is not unique as far as this composite record "view" is concerned. So when creating the view, start with the detail record and then join the master. This should just all work for lists and views (both directly in SQL and PHPR). Rearrange the fields so that it makes sense to the user.
    The hard part is the writing.

    When adding a new record, then things get tricky. This DB doesn't know how to do a multi-table add in one step, and PHPR doesn't know. You have to provide the rules. First of all, when doing an add, you have to check if the master record already exists, or if you allow 1:N relationships. If the master key exists we can display the master fields, leaving the user to just fill in the detail fields. I always use a unique auto-key for the detail record index, and just let the DB supply that in the add. If you just try and let the multi-table "add" process as normal, you will get a runtime error, so you need to prepare the event. I usually let PHPR do one half and programmtically do the other half, but you could programatically do both halves (or could even be more than 2 tables involved in the update). In the event BeforeRecordAdded if the master already exists, then you have to skip that part and only add the details, otherwise you add both. Validate everything beforehand so that it doesn't fail halfway through. After you've finished the programmed table add, use UNSET($values[".."],..) to remove those fields from the $values array, and then let the event finish normally.
    When editing/updating a multi-table view, its even more tricky if we want to use the same form. You start to understand why DB designers have put this feature into the "too hard" basket. When trying to update fields in the underlying master record, this depends on whether there are other composite records sharing the same master. I just don't let the user change master fields in a multi-table edit. If the master is already shared with other details, then I make these fields read-only. If they need to change master fields they they do it the normal way, and I provide a direct link for that. However, if there are no other details (yet), then maybe they could change all the fields on the same screen and save a step(s). Then we get on with updating the details in the BeforeRecordUpdated event, and use the UNSET to remove the fields you programmatically update, so PHPR runtime/SQL doesn't get confused and generate a runtime.

B
btrdev author 7/11/2011

Thanks for the responses.
If this is a limitation by design, then it's a bit disappointing. It means I now have to find some more complex workaround for what I thought would have been a simple user interface feature.

E
electromotive 7/11/2011



Thanks for the responses.
If this is a limitation by design, then it's a bit disappointing. It means I now have to find some more complex workaround for what I thought would have been a simple user interface feature.



Database engines available before SQL used to be more rich. SQL has been a dumbing down. However its portable, fits in with an Excel-like view of the world which most people will understand.
There are other solutions which might fit into the available technology better. If the relationship is always 1:1 (or 1:0), then just use one table in the first place. Otherwise use the built-in master-detail mechanisms and either train the users to click on the detail link and add after the master has been added (three extra clicks), or put in events after the master is created to take the user directly to the detail add form, or add a button on the master add/update form which takes them directly to the detail add form, or start with the detail and use a drop down to select the master key, and if the master is not there then use the "add new values on the fly" option which will open a popup master add form. I think all solutions which support 1:2+ relationships will involve at least two forms, and some clicks, unless you try and use multi-table views.