master detail forms

[mikesimaska]

Here's my app in a nutshell:

3 tables (all external to joomla)

units - marching bands, winterguards, etc...
id PK
name
type 0=outdoor 1=indoor
events - competitions of units
id PK
name
type 0=outdoor 1=indoor
date
entries - participating units (and their scores when the event is complete)
id PK
unitid FK units.id
eventid FK events.id
score
awards
completed boolean

I've been making good progress using the default stuff and not digging into many details. Now I need to do some real "application" type stuff.

I need a form in which I can

Select An Event (a combo box is ok for now -- i want to be able to filter events by date eventually)

If the Event has Entries:
Display the entries with 2 text boxes next to each entry for the scores and awards fields. If data is entered in a text box, the record should be flagged for updating.
Have an "Update" Button which posts the scores and awards back to the entries table and sets the completed flag to True for those entries.

If an Event has no entries,
Let me add multiple units to the show in a single form
Ideally this would be unlimited but i'll settle for X number entries added at time (for now!)

Note there are two types of unit (indoor and outdoor) with a corresponding field in events. Only outdoor units can attend outdoor events (and vice versa).

<aside> If I add fields to a physical table do I need to start over on my Fabrik stuff or can I simply add new elements? </aside>

 

[cheesegrits]

Fabrik version?

-- hugh

 

[mikesimaska]

Fabrik 2beta SVN 619
PHP Built on: FreeBSD 6.2-RELEASE-p1
Database Version: 5.0.67-log
Database Collation: utf8_general_ci
PHP Version: 5.2.6
Web Server: Apache/2.2.9
Web Server to PHP interface: apache2handler
Joomla! Version: Joomla! 1.5.7 Production/Stable

 

[cheesegrits]

Hmmm. Displaying different form views conditional upon the existence of joined records isn't something we support out-of-the-box. But it could be achieved using a form plugin or perhaps a custom template ... although either of these approaches would require some PHP knowledge.

Unfortunately, as per the Notice at the top of the page, this week is not a good week for me helping out with this kind of stuff. You'll probably have to bump this thread next week, when things will hopefully be a little less crazy!

As for the question about adding columns to an external table ... that's a good question, and one which I don't know the answer to. I'll have to test and get back to you on that one.

-- hugh

 

[mikesimaska]

Hmmm. Displaying different form views conditional upon the existence of joined records isn't something we support out-of-the-box. But it could be achieved using a form plugin or perhaps a custom template ... although either of these approaches would require some PHP knowledge.

Hmmm. Ok. Suppose it's not conditional? Suppose it's just a form that

when selecting an event

a)display all the units currently assigned to that event
b)let me enter scores for the those units as above
c)save button updates all changed rows


Of course now I need a second form that

when selecting an event

a)display all the units currently assigned to that event
b)let me add and remove units faster than one at a time

Does that help the app fall more into line with current capabilities of Fabrik?

 

[cheesegrits]

I'm going to have to punt this till next week. I'm out of town this weekend, Rob is away till Monday, and I'm desperately trying to get caught up on some issues ahead of you in the queue.

Short answer is that what you are asking is not going to be trivial, even with the new features in 2.0, and will almost certainly require some kind of plugin work.

-- hugh

 

[mikesimaska]

is there any docs or sample plug-in i can look at? as i said i'm not afraid to get hands dirty if i have a vague notion of what i'm doing. i'm not even sure *why* it requires a plug-in

 

[cheesegrits]

Not something I can summarize in 12 Words Or Less. I think the model you have in your head mixes aspects of what Fabrik knows as Forms and Tables.

It would help both of us if you could mock up how you see the main pages of your app looking. Apart from anything else, it's really hard to describe these things in words ... what you see in your minds eye and what I picture from your description could be miles apart ... and a picture, as we know, is worth a thousand words.

-- hugh

 

[mikesimaska]

PM'd you a link

 

[mikesimaska]

pm'd you a userid /passwd so i can keep the site offline except for developers

 

[mikesimaska]

*bumpity bump bump*

 

[cheesegrits]

I'm still pretty much snowed under till Rob gets back tomorrow.

-- hugh

 

[mikesimaska]

*bump* 1 week later

 

[cheesegrits]

Thanks for your patience.

The main sticking point, in either of your scenarios, is that we don't support any kind of modifiable table view. That is, we have tables (row based, read only lists of form content presented in tabular form), and forms (editable forms, comprising one 'row' from a table).

So there's really no way to do the kind of 'batch' adding / editing of records you are wanting to do.

Your basic app could be built in Fabrik, but adding / editing of units joined to the events table would have to be done one by one, rather than in 'batches'.

-- hugh

 

[skh]

Hey Hugh,

Not sure if this is helpful, but we put together a master/detail app in the .NET world where we utilized ajax to refresh the modules which allowed us to not do a "batch" update, but provided an inline style of updating and adding records. Might give you a few ideas...or mikesimaska a direction to look into...

A picture is worth a 1000 words...

Included is edit mode, showing only the fields that are editable in the table...hth

Cheers,

Keith

 

[cheesegrits]

We do have Ajax based form submission using Mocha popups.

Create a database join element on one of your related tables, pointing back to the main table. Then on the main table, at the bottom of the 'Data' tab you'll see "Related Data". Enable the 'form' option. Your main table will now have an "Add" link which will create a new form for the related table, using a Mocha popup.

-- hugh

 

[mikesimaska]

I'm not stuck on editable table views -- its just the best description I could come up with given my limited Fabrik expertise. Let's try another description....

Imagine a Fabrik Form where.....

xxxxxxxxxxxx pull down select box of possible events

1111111111111111111111111111111 O detail lines for units registered for this event
2222222222222222222222222222222 O followed by checkbox to select a detail line
3333333333333333333333333333333 O
4444444444444444444444444444444 O

A)dd add a new unit to this event
D)elete the selected units
C)hange edit the selected unit
S)core display a form with several elements that repeat for each unit currently scheduled
for this event.

Is that more in line with Fabrik's capabiliites? Pop-ups sound like a good solution. I also have some sneaking suspicion that somehow that repeating option in forms could be useful to me.

 

[cheesegrits]

If I'm understanding you correctly, it might be possible to build a table display like that, but it would require some extensive table template customization (to flip it from being row oriented to column oriented).

And yes, you can use a repeat group to do one-to-many joins. It's not quite as simple as just enabling group repeat though. See this thread:

http://fabrikar.com/forums/showthread.php?t=6015&highlight=widget+gidget

... which shows you how to build a one-to-many relationship between a 'widgets' table and a 'gidgets' table. NOTE that this only works if it is a one-to-many relationship, whereby a 'widget' can have multiple 'gidgets' related to it, but each 'gidget' can only relate to one 'widget'.

-- hugh

 

[mikesimaska]

wow. i really didnt think i'd get nowhere so fast lol!

let's back off to a simpler problem:

can i build a fabrik form where a single pull-down element causes an associated table view underneath to update?

Choose Event: XXXXXXXXXXXXXX (a pull-down element populated with events.name -- selecting eventid causes a query to execute selecting * from entries where entry.eventid=pulldown.eventid)


unit name group class url
xxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
xxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
xxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
xxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
xxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx


If I can get to this, i can make the url link to a form for that entry right?

 

[mikesimaska]

Just following up on my last post -- I really think that's the best "fabrik" way to approach this...the form will have have a pull-down populated with possible events. It should also have several buttons (add, change,delete) which act on the currently selected item in the tableview. When the user selects an event from the pull-down it will trigger the display of table beneath the form. The table will be generate from a SQL query on the entries db joined to the event.id from the pull down.

This way I'm not changing the Fabrik paradigm of tables being row-based read-only objects. What I haven't figured out is how to get the form to interact with the table. I know there's going to be some ajax involved. I'm reading the forum and I saw FAQ entry on "How to do condition drop-down's that link to a database" It outlines the ajax but doesnt quite explain how i might get that ajax to show a fabrik table.

Sorry I'm being such a pain in this thread. OTOH I think master-detail is support is going to critical to any real-world app. So I'm hoping expedition yields useful results for everyone.

The end result ( a row-based table filtered by a form ) is really not so weird. The whole back-end of jooomla is based on a row-based display of data with simple support for ADD DELETE CHANGE and filtered views.