Repeat Group Calc Value of Two Elements Based on Other Values in Same Group

[eugenecjr]

What I am doing seems pretty simple, but somehow I am not able to get the sequence of events or settings for the elements to do what I am looking for.

All are working in a repeat group where the fields involved include a count, reorder_count, inventory_status and status.

What I am doing is determining the status of both the inventory status and status based on a comparison between count and reorder_count.

If reorder_count is less then Count I want the values to be inventory_status = 'Stocked' and status = 'Closed'
If reorder_count is greater then Count I want the values to be inventory_status = 'New Request' and status 'Open'

To explain what happens, we have 5 locations where they do what we refer to as counts. When employees enter counts they are adding a new records to the repeat group and entering how many of that item they have. As indicated above the count is compared to the reorder count to determine if more of that item needs to be ordered or not.

Based on the status being set we have another form that a manager uses that only shows items that have a status of 'New Request'. Once an item is ordered the inventory_status is set to ordered, allowing employees to see that the item has been ordered.

When the item comes in the employees will then go in and mark the item as received.

Finally to monitor lead times and determine if a store has received items that have been ordered the manager will look for any items that have been marked as received and close the out removing them form all list seen by the employees or managers.

The reason for this design is for a single system for employees to put in counts that covers 5 locations and a single manager who is responsible for ordering all items for 5 locations.

THE PROBLEM: I can get the status to set but but am having issues with the calculate element resetting or clearing both status each time reorder_count and count are evaluated. I need to find a way to only set the status for new repeats and not touch any previous or already saved repeats.

 

[juuser]

With your setup, I would definitely not use repeat group for item counts. I would create another joined list/form for that and link it from the "main" list e.g. with list link plugin.

I think it might be quite tricky to determine in calc element which repeat row is added after last saving the record. Cannot think of a way right now. This of course doesn't 100% mean that are not any.

 

[eugenecjr]

juuser, thanks for your reply.

It appears that this will put a button on the page which I would link to what is now the table setup as the repeat group. The gray area is that being that the current table that holds the counts is part of a repeat group it does not have a list, so seems I would need to create a list and make it accessible to Joomla via a hidden menu item to have put in as the link. What is also unclear is the relationship between the two tables as it is now with the repeat group and how that will exist using the list link.

I apologize this is not exactly clear to me.

-Eugene

 

[juuser]

Yes, you would have a button/link in the list linking to other list or form.

In your second table you can create a databasejoin element e.g. called parent_id referring to your "main" table's id element. And if you need list join, you would join like table1_id -> table2_parent_id.

And when creating new record in second table from the link of the main table, you can set the parent_id element value like ....&secondtable___parent_id_raw={maintable___id_raw}...

 

[eugenecjr]

juuser,

Based on the current setup sounds like I have everything setup as indicated. Table A with Items Table B with Counts Table A's list is joined to Table B (Table A id --> Table B parent_id). This is currently what has created the repeat group of counts linked to the main table of items.

Where you loose me in this is the creating the new record and how that looks, when I am assuming you clock the button. For the list-link plugin I specify a Access, Button Label, Link, where to show in a new tab, if the link is to Fabrik, and a Popup Title. Not sure where I am to set the parent_id element value. Also am I setting up the list-link on Table A or Table B. Based on the initial post I thought it was Table B and then specifying Table A's id as Table B's parent_id.

Having a hard time visualizing how this works to understand exactly what needs to be done. I am very interested in figuring out this list-link since I have never used it before and how I might be able to use it for other things we are doing. Again thank you for your patients and help with this.

I am wondering if it would be easier to create a stored procedure that runs when new records are created to set the appropriate status as indicated in the first message. I don't like spreading things out, but seems it would be a possible solution.

 

[juuser]

In principle yes, but not exactly. Repeat group is part of Table A and there is no separate list/form for that.

Based on the current setup sounds like I have everything setup as indicated. Table A with Items Table B with Counts Table A's list is joined to Table B (Table A id --> Table B parent_id). This is currently what has created the repeat group of counts linked to the main table of items.

In my suggested setup, new record to Table B is added from the list link plugin in Table A . You can copy the "Add new" link from Table B, paste it to link plugin in Table A and append "&tableb___parent_id_raw={tablea___id_raw}" to the link at the end.

Where you loose me in this is the creating the new record and how that looks, when I am assuming you clock the button. For the list-link plugin I specify a Access, Button Label, Link, where to show in a new tab, if the link is to Fabrik, and a Popup Title. Not sure where I am to set the parent_id element value. Also am I setting up the list-link on Table A or Table B. Based on the initial post I thought it was Table B and then specifying Table A's id as Table B's parent_id.

Why not, although I personally would use form php plugin to update the statuses.

I am wondering if it would be easier to create a stored procedure that runs when new records are created to set the appropriate status as indicated in the first message. I don't like spreading things out, but seems it would be a possible solution.

P.S. As I'm not able to visualize your setup 100%, these are just a suggestions and approaches that might be possible to use. So these may or may not be the best solution in your exact case.

 

[eugenecjr]

juuser,

Your explanation has cleared things up for me and makes perfect sense. I could not agree with you more and is why a community like this is important. We all have various experiences and levels of experience and sharing ideas his how we expand on what we know. I would have never thought to try your solution and whether it works for this application or not I have learned something new.

-Eugene

 

[juuser]

Great, and with this setup it's usually a good idea to activate "Related data" option in Table A list settings for an add link / related records link-count to appear in the rightmost column of the list.

 

[eugenecjr]

juuser, this is a little off topic but something that came to mind with regards to using list - link. We have been talking about using it to add a new record by clocking on the "Add new" link. Can the same be done with list - link to open an existing record and how would that look?

 

[juuser]

Yes, of course. Just copy the edit link from the list and replace static numeric rowid with {rowid}