Unable to Get Form RowID to write into Another Database

[eugenecjr]

I am using a Form PHP plugin. When I open the form I want to be able to create records in another database table that is also the sub form for the same form with the PHP plugin.

I have successfully created new records in the sub form database however I am not able to get the rowID of the main form so it can be assigned to the database of the sub form.

I have tried several suggestions but var_dump does not show the row ID.

I have tried with both onBeforeLoad and onLoad.

Thanks in advance for your assistance.

-Eugene


Sent from my iPhone using Tapatalk

 

[lousyfool]

When adding a new record, or when editing an existing one?
With a new record, you can't expect having a row ID, because it doesn't exist yet and is only "generated" when the form is submitted.

 

[eugenecjr]

Lousyfool,

I was suspecting that might be the case. So what is the best way to figure out what the ROWID will be vs what it is. The new record that is being added is a subform so I will need to include a 'parent_id' with the new record. The form I am using the PHP plugin with is the parent form. Ideally I will be starting a new parent form then creating a number of children records in the subform that have some information already filled in. The user will just be putting counts in for each of the records in the subform.

-Eugene

 

[lousyfool]

Well, that's a tricky one. If you had guaranteed only one single user who would only add one single record at a time, you could theoretically use a query to get the most recent (= currently highest) ID of the table, then add 1. But, think about it, only under such stringent conditions.

 

[eugenecjr]

Yes, and although that would be the case most of the time it would not be a guarantee. I am not sure the best approach otherwise to add new records to a forms subform. In summary what I am looking to do is the following:

Table A - Reference table
Form B - Main Form
Form C - Sub form for Form B

1. 'Add' new record Form B
2. Look in table A to get a count of records where date of week = current day of week.
3. 'Add' new records in Form C based on the count at step 2, for each add item for each record in Table A to Form C.

The user is then presented with a Form B containing a number of records in Sub Form C that have items identified that the user will enter counts for. Below is a visual of the results.

FORM B
- Date/Time
- Employee
- Store
FORM C
-Item (ie. Happy Birthday Balloon) - Count <User entered data>
- Item (ie. Thank You Balloon) - Count <User entered data>

What we are looking to do is take what is over 100 hundred items and divide them over the days of a week so employees only have to inventory a small amount each day.

As you can see I am getting stuck with getting things going.

Thanks again for your help.

-Eugene

 

[lousyfool]

Provided you're not dealing with a huge number of records and the IDs , let's keep it simple:
When adding a new record, instead of "knowing" the next ID, why not simply "set" it instead?
You could generate an random integer (say in between 1000 and 99999999999, as INT(11) is the default for the PK, and which such range it being a tiny risk that the same random number is generated by two users within a small time window), check if it doesn't already exist (otherwise generate new numbers until that's the case), then set this as the new row ID.

It's not 200% safe, obviously, and I haven't needed to do this myself yet, but should work.

 

[eugenecjr]

Could possible create a unique number structure by doing some a little simpler example 3/23/2020 3:00:35 PM In this case the only issue would be if two people happen to create records at the exact time down to the second. such would look like this 032320150035 this would be an int(12) and if you were not sure that a second is not enough could take it to an int(14). In that case I know I can set the parentID of the child record but can I set the ID for the parent even though it is autogenerated? I have never attempted and thought I would ask as I am typing this.

-Eugene

 

[eugenecjr]

Based on the issue with the ID I got to thinking that maybe I can use the 'Apply' button as a form of 'Form Generation' where I will commit the main form so I have an ID to work with, that can be applied to the subform records as the Parent_id. I am using the following code:

$myDb = JFactory::getDbo();

$myQuery = $myDb->getQuery(true);

$myQuery->insert('fab_inventory_checker_194_repeat')

->set('item = ' . $myDb->quote('lead'))

->set('parent_id = ' . $myDb->quote($myId));

$myDb->setQuery($myQuery);

$myDb->execute();

$myId = $myDb->insertid();

That is applied which is executed onAfterProcess.

The issue is that I get two records. The first has a parent_id but no item and the second does not have the parent_id but has the item "lead".

Strange since I believe I am executing just one insert statement where I am setting both values at the same time.

Thoughts,

-Eugene

 

[eugenecjr]

Nevermind, the answer is so stupid that it is right in front of me. However, wondering if I am making any head way. Now, I have a record that is the initial record that is being saved with the ID and the record that is actually being created by my PHP in the PHP Plug in that has gotten me no further as it does not have the ID at all. frustrating.

 

[lousyfool]

Could possible create a unique number structure by doing some a little simpler example 3/23/2020 3:00:35 PM In this case the only issue would be if two people happen to create records at the exact time down to the second.

Well, my idea of randomly generated IDs was rather spontaneous, and yours here based on current time sounds good to me, too. You could simply use the current time in Unix Epoch format, then you have it as a fitting integer.

With your following two posts you're somewhat losing me, because you had started off all this with needing the ID (or maybe a different unique identifier?) on form load and also when adding new records. If this requirement has changed since and using onAfterProcess is ok, then you have an ID also for brand-new records because by then it's been stored - incl ID, of course.

 

[eugenecjr]

Sorry about the changes, not trying to loose anybody, just actively trying to get this going. I have been stuck in the same spot for some time trying to figure out how to create sub form records. If I try to generate a random ID 'Before' I can't seem to get it posted to both the ID of the main form and the child of the sub form. If I try doing this after then the issue is that I get two records, the one created by default when the form is opened and a second with what I have created, howerver, I am not able to get the child ID updated in the record I am creating. I end up with on record with the child ID and no additional data, and another with the data I am trying to input using my PHP code but no child ID. If I am missing something it is little and enough that I am chasing my tail.

 

[lousyfool]

I'm not entirely clear what you mean by "sub form", "child of sub form", "child ID" etc. You seem to be wanting to populate a different table ("B") with data from one form (of list/table "A"), and that even when adding new records to A, and before or while the form is loading. Generating a yet non-existing ID shouldn't be a problem, and neither should be to make this part of the other data you seem to have for populating B. No clue what code you're using for this and where, though.

Alternatively, have you thought of, or tried, joining A and B on list level? You could still hide any or all of B's fields from form A, if that's what you need.

 

[juuser]

Also note that if you add records to your "sub-form" with a php-form plugin from your main form and the user forgets or decides not to submit the form, you will have a lots of "trash" records in your "sub-database". I'm still sure if you would have taken the way I suggested in other thread where you asked the same question, your would already have forgotten this issue and be dealing with your next challenges

 

[eugenecjr]

I looked a the solution with regards to filtering and what not but am not 100% sure where exactly how I would do what you were suggesting.

If you could provide a little more detail that would be great.

The avenue that I am looking at not only serves what I am looking to do with this project but some other ideas.

Also I think my use of the term inventory is leading to thinking I need to keep counts of what is on hand and will be taking away inventory sold.

The purpose of the form is to see what we currently have on hand so items can be ordered. Our system used for customer orders is closed and will not have access to the data that would allow me to maintain a true inventory.


Sent from my iPhone using Tapatalk

 

[juuser]

As I understand, you have your stock balance for each item in your main table. So you could:

1) make a copy of your main list;
2) add list pre-filter: where "your_inventory_day" = "today";
3) add second pre-filter: where inventory_maker = "currently_logged_on_user";
4) add "inventory made" date element to your list, update the date when user changes the stock amount. If you have only one editable field (amount) in the copy of your list, you can use date element setting "always return todays date";
5) add one more prefilter to your main list: where "inventory made date" < "today";

and

6) add inline edit to your list if you want for the user to be able to update the stock amount more easily by clicking on list cell (optional);
7) use form "log" plugin to track the changes (optional).

or

6) make another related table where you update the stock balance value in main table with form php plugin. In this case you can have the history in the related table. Although inline edit straight in your main table would probably be more convenient for the user.

 

[eugenecjr]

Thanks for the additional details. I will be running through those exact steps and will let you know if I run into any road blocks. The additional detail provides a clearer picture of what you were referring two initially.

 

[eugenecjr]

1) completed
2) completed
3) I assume this is in the main inventory list where I need to add a new element to house the person that last updated the inventory counts?
4) 'inventory made' as a date element is to put in the date the inventory was last updated? The issue here is that I actually have three locations that all have their own items that need to be counted to determine if anything needs to be ordered. With how I was looking to do this initially this was something that was a drop down in the main form and not part of the sub form where I was having issues which is why I had not brought it up.

Want to get your thoughts on that before moving on. I will continue to look at what I have with this new list copied from the main list to get a grasp on how this all plays together. I have not done any copying of list and understand that they are working on the same table in the db.

-Eugene

 

[juuser]

For (3) I understood that for every item you have assigned an employee who is doing the counting? With that prefilter, logged in user sees only the items he/she should count

4) With this I meant you have a "counting date" for every item after the amount has been updated. Just to make sure, you have only one location and counter for one item, not several?

Yes, when coping the table, it uses the same database table. In the copied table you can just e.g hide some additional elements, have different settings for some elements etc.

 

[eugenecjr]

3) Okay, I see what you were looking at doing for each item, they are actually not assigned to users. Anybody that is in the store that day, and it will be different week to week, can do the inventory. The key is that the inventory is assigned to that day.

4) I actually have 3 different stores that all have their own inventory. So each day I will have three people that would be opening this form to enter their store's counts for each item presented for that day of the week.

Since my last post I have hidden the elements that are not necessary. I have used inline edit before and seems that would have to be the way to go because if they open the record they see all of the fields associated to that record which is not necessary. They just need the item and a place to put in the count.

 

[juuser]

3) Ok, somewhy remembered you assign the certain item counting to certain users. Anyway, you can skip this in this case.

4) If you have several stores for one item, I recommend you to have separate rows for items in separate store something like this:

Item Location Amount
item1 store1 120
item1 store2 150
item1 store3 75
etc.