Many to many join update existing records

[AndyR]

Hello,

I'm still getting to grips with how Fabrik works so forgive me if there is a simple explanation to this:

I have a pair of tables - 'Offices' and 'Documents', and I have then joined with a databasejoin element so that I have a form with a dropdown 'select office' box, then a list of checkboxes where I can select which documents are relevant to that office (for clarity there are approx. 40 documents and 10 offices, each office will need maybe 20 documents from the list. The same document can be needed at more than one office)

This all works and saves the data correctly in the list, and i can click on an set of data to edit it, but what I want to do is this:

• It should only be possible to have one set of data per office - currently I can add another if I want to
• Although the list view works for adding / editing the data, I want to be able to select the office from a dropdown list and have the document checkboxes populated with the existing document data for that office if there is any, then amend if necessary and save.

I could create this in php but as I've got quite a few similar forms to create I'm thinking there is probably a way to do this with Fabrik and maybe I just need a nudge in the right direction.
Andrew.

 

[cheesegrits]

I'm a little confused on the setup.

Do you have a third list/form, on which you select the office / documents?

For the "one set of data" and "currently I can add another" ... this sounds like you have enabled "repeat" for a join?

If you can point me at page(s) on your site you are working on, that would help me visualize your issues.

-- hugh

 

[AndyR]

Hello Hugh,

Do you have access to the 'sites' in my profile? I don't really want to publish the link here as it contains potentially sensitive data.

I have a list called 'offices' which is just the office name and id so I can add & remove offices, then I have a 'documents' list which is the same sort of thing for the documents with the addition of an 'order' field so I can show them in the order I want. The third list just connects the two with a pair of databasejoin elements where 'Offices' is a dropdown to select the office, and 'document' is a list of checkboxes to select the required documents for that office. I'm aware this is probably the wrong way to do what I'm trying to achieve, but I'm just finding my way around Fabrik at the moment.

<edit>
To try and clarify: In php / mysql I would:
On selection of the office, check for existing document records for this office and load them into the checkboxes.
On submission check the existing records for this office and delete / insert them as necessary.
</edit>

Andrew

 

[rob]

The third list just connects the two with a pair of databasejoin elements

dumb question here - but which list is the 'third list' ?

It should only be possible to have one set of data per office - currently I can add another if I want to

You could add an 'isuniquevalue' validation rule (http://fabrikar.com/forums/index.php?wiki/is-unique-value-validation) to the office element

Although the list view works for adding / editing the data, I want to be able to select the office from a dropdown list and have the document checkboxes populated with the existing document data for that office if there is any, then amend if necessary and save.

I think this would require some custom coding using the user ajax system we provide http://fabrikar.com/forums/index.php?wiki/user-ajax/ Let me know if you need a hand with working out the js / php to use for that.

 

[AndyR]

Hello Rob,

The 'third list' is the list that joins the documents list to the offices list - it consists of a dropdown databasejoin for offices and a set of checkboxes for the documents.

If it makes any difference, this just sets up the documents that are required for each office. When I get this working correctly I will be creating another form which selects an office, selects a member of staff from that office and then pulls in the list of staff documents required for that office, with inputs for the status of each document (present, missing, etc. and a text box for comments) so that each member of staff's file can be checked and the documents ticked off on the list. I then generate a report for each office & the whole company.

I have all this part working already on the existing live site which I hand coded (quite roughly) into Joomla using DirectPHP, but with the next version I was hoping to neaten the code up and cut as many corners as possible to make it easier to administer in future. Fabrik has been great so far for generating the inputs like lists of staff, offices, documents, etc. that I used to just update directly in PHPmyadmin, but I'm struggling with the more complicated bits at the moment.

I tried using 'isuniquevalue' on the databasejoin for the offices but I got some strange errors so I thought I must be doing something wrong. I will try again.

I will have a look at the user ajax page and see if I can work it out. I'm prepared to hand code the whole lot again if necessary as a last resort, but I was thinking this was a fairly standard set of requirements and maybe there was a simple existing Fabrik based answer that I'd overlooked.

Andrew.

 

[AndyR]

Update:

I created a new list with one databasejoin dropdown element linking to the 'offices' table, and added 'isuniquevalue' validation, and a second element with a databasejoin of type 'checkboxes'. The 'add' screen looks fine (see attached screen grab) but when I click 'save' I get the following error:

Warning: Missing argument 3 for PlgFabrik_ValidationruleIsUniqueValue::validate(), called in /home/gnitset/public_html/dms/components/com_fabrik/models/form.php on line 2262 and defined in /home/gnitset/public_html/dms/plugins/fabrik_validationrule/isuniquevalue/isuniquevalue.php on line 60

Warning: Missing argument 4 for PlgFabrik_ValidationruleIsUniqueValue::validate(), called in /home/gnitset/public_html/dms/components/com_fabrik/models/form.php on line 2262 and defined in /home/gnitset/public_html/dms/plugins/fabrik_validationrule/isuniquevalue/isuniquevalue.php on line 60

Fatal error: Call to a member function getElement() on a non-object in /home/gnitset/public_html/dms/plugins/fabrik_validationrule/isuniquevalue/isuniquevalue.php on line 68

This is the error I referred to previously.

I can get the thing 'working' for now by removing the validation, but eventually I have to let users loose on this and I just know it's not right yet. Plus there will be other forms & tables relying on the data from here so I don't want to have to change it all later.

Andrew