See record in another table after status change

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

See record in another table after status change

Post by aarlauskas » 2020-03-05 16:46

Hi, does anybody knows how to make the following scenario work?..:

Two tables

1. Table: stock_available
a. Field: item
b. Field: comment
c. Field: status(drop-down) options: available/not available

2. Table: stock_not_available
a. Field: item
b. Field: comment
c. Field: status(drop-down) options: available/not available

When I receive an item, I want to scan this into stock_available table and select status ‘available’.
When I use this item so its not longer available, I change the status to ‘not available’.
What I need is, when I change the status to ‘not available’, it will disappear from ‘stock_available’ table and will be displayed in ‘stock_not_available’.

Can somebody help with this please?

Thanks.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: See record in another table after status change

Post by jsetzer » 2020-03-05 16:58

I recommend using one table only and adding a new column like quantity. Then you don't have to insert and delete records but just have to change the quantity value.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

Re: See record in another table after status change

Post by aarlauskas » 2020-03-05 17:38

Thanks Jan, but that is not the case. If it was the same items that only quantity that matters, then yes. This is for item with serial numbers, so each item has its own tag. So I need to track where each specific item was used. At the moment I have this set with 'traffic lights' on row, based on status field: available (green)/not available(red). I can also filter this of course based on that field, but I really want to make it simple, so it displays under different table when no longer available. If I need to trace the history of the item, I can simply go to that other table. I also want to share this with my other fellow work coleagues, so simple solution would be great. You did a great job on complete/incomplete field, when status changed to complete it changes the ownership to specific user. I was trying to make this work by integrating that and maybe creating views in db somehow, but with my lack of experience could not get this to work.. Thanks Jan.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: See record in another table after status change

Post by jsetzer » 2020-03-05 18:27

As I said: it is only a recommendation from my experience. If your usecase for any good reason requires redundant, non-normalized data storage, keep going. There may be good reasons.

Please consider:
Having an integer (or even decimal) quantity field instead of a boolean field (on-stock: no/yes) can give you more flexibility for the next years with different types of articles. But of course this is up to you. I am just sharing my experiences with PPS and ERP systems with you. Most of them have a decimal quantity field plus a unit field. This allows not only yes/no and not only integer quantities, but also values on stock like "3.5 Palettes" or "0.75 kg" or "1.5 man/days" or "0.0003 g" (yes, 15 years ago there was a customer producing taste for cigarettes and they were calculating with fractions of milligrams).

From information point of view there is no difference between...

onstock=true
and
quantity=1

It is the same information.

Also...

onstock=false
and
quantity=0

is the same information.

Please don't get me wrong. There may be good reasons in your case for solving a common ERP requirement differently.

Anyway, afterInsert and afterUpdate of 1st table, check the status. Use SQL to insert the same data into 2nd table and set-record_owner(). Update status of record in 1st table.

If necessary, do the same vice versa on 2nd table.

Regards
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: See record in another table after status change

Post by pbottcher » 2020-03-05 20:32

Hi,

you discribed the solution already. Just make two views of the stock_available table (with an applied filter through the hooks/links-home.php and links-navmenu.php

This will show up like a table each and you will have all the data immediatly displayed correctly without any additional work.
If you want to not show all data in the stock_available table, just dont show that one.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

Re: See record in another table after status change

Post by aarlauskas » 2020-03-06 22:27

Hi, Thanks for this as always! I think this definetely works as it should without too much hassle. I did create a view of the first 'stock_available' table, then just used hooks/stock_available.php and entered the below code under _init

Code: Select all

	function stock_available_init(&$options, $memberInfo, &$args) {

	/* Apply a default filter only if no filter is already applied by the user */
    if(!$_POST['FilterField'][1] && !$_GET['FilterField'][1]){
        /*
            In the call below, we want to display records of the stock_available 
            table where the value of the 5th field is equal to 'Available'.
        */
        addFilter(1, 'and', 5, 'equal-to', 'Available');
    }


		return TRUE;
	}
So basicaly field 5 (count from the top in your appgini project file) is my 'Status' field with options Available/Not Available.

I then applied the same code on my 'view_stock_available' table and just changed to option to 'Not Available'.

Then I back to Appgini program and un-ticked both oprtions 'Allow Filters' & 'Allow Users to Save Filers' and generated new app again, so users cant mess with filters.

Now I have two separate tables, one displays what is available in my stock, other what is no longer available.. Genius :D

THANKS PASCAL! I Knew I was close :lol:

User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

Re: See record in another table after status change

Post by aarlauskas » 2020-03-06 23:06

Ok, so there is a small glitch somewhere. When I change the status of the record to Not Available, it then goes away from stock_available table and shows up in stock_not_available table, no problem, all data of the record is displayed fine in table view. But, when I click on the record to access detail view, the lookup fields no longer display any data. All the rest of the fields seem to be ok, but not the lookups. If I change the status back to Available, then lookups show fine under stock_available table again :roll: I have attached YouTube link below. Any ideas? I run this currently on xampp for testing, not sure if the same happens online yet.

https://youtu.be/oMZmH6KIgVM

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: See record in another table after status change

Post by pbottcher » 2020-03-07 08:36

Hi,

can you check if you have an error in the development console when you call the "not available" detail view. Or do you have any additional code?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

Re: See record in another table after status change

Post by aarlauskas » 2020-03-07 15:19

Hi, nothing in console and there is no any additional code.

So basically, I have created a VIEW of the first table in phpMyAdmin, then in Appgini program done copy/paste of that table and renamed the new table as per view name (view_stock_available). Applied different filters for both based on available/not available. The filters does not play any role, even with the filters removed the new VIEW table does not display any data in lookup fields.

So I've changed three lookup fields in the new VIEW table to basic VarChar and set them as read only. What I can see now is that these fields display ID of the parent instead of actual field value like in original table. So this means it is something to do with sql query somewhere, the lookup fields in the new VIEW table display ID instead of value. How can I fix that? I dont really want to lose lookup fields as I was planing to add new items on daily bases (makes & models) etc. This is obviously something stupid and you probably know the answer, however this is new to me :D

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: See record in another table after status change

Post by pbottcher » 2020-03-07 15:49

Hi,
not sure why you created a view.

To summerize my understanding:

You have one table stock_available

What you need to do:

Put this table to hide in tableview and hide in detail view in the APPGini definitions.

Now you create two links in the hooks/links-home.php.
Something like

Code: Select all

	$homeLinks[] = array(
		'url' => 'stock_available_view.php?SortField=&SortDirection=&FilterAnd[1]=and&FilterField[1]=5&FilterOperator[1]=equal-to&FilterValue[1]=available', 
		'title' => 'Stock available', 
		'description' => '',
		'groups' => array('*'), // groups allowed to see this link, use '*' if you want to show the link to all groups
		'icon' => 'table.gif',
		'table_group' => 0, // optional index of table group, default is 0
	);

	$homeLinks[] = array(
		'url' => 'stock_available_view.php?SortField=&SortDirection=&FilterAnd[1]=and&FilterField[1]=5&FilterOperator[1]=equal-to&FilterValue[1]=not available', 
		'title' => 'Stock not available', 
		'description' => '',
		'groups' => array('*'), // groups allowed to see this link, use '*' if you want to show the link to all groups
		'icon' => 'table.gif',
		'table_group' => 0, // optional index of table group, default is 0
	);
Now you create basically the same entries in the hooks/links-navmenu.php file
Something like:

Code: Select all

	$navLinks[] = array(
		'url' => 'stock_available_view.php?SortField=&SortDirection=&FilterAnd[1]=and&FilterField[1]=5&FilterOperator[1]=equal-to&FilterValue[1]=available', 
		'title' => 'Stock available', 
		'groups' => array('*'), // groups allowed to see this link, use '*' if you want to show the link to all groups
		'icon' => 'table.gif',
		'table_group' => 0, // optional index of table group, default is 0
	);
	$navLinks[] = array(
		'url' => 'stock_available_view.php?SortField=&SortDirection=&FilterAnd[1]=and&FilterField[1]=5&FilterOperator[1]=equal-to&FilterValue[1]=not available', 
		'title' => 'Stock not available', 
		'groups' => array('*'), // groups allowed to see this link, use '*' if you want to show the link to all groups
		'icon' => 'table.gif',
		'table_group' => 0, // optional index of table group, default is 0
	);
now you should be set.

You need to verify the Filterfield (I used the 5 from your example.

No need for any other changes (I would assume)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
aarlauskas
Veteran Member
Posts: 127
Joined: 2019-04-28 18:03
Location: Medway, UK

Re: See record in another table after status change

Post by aarlauskas » 2020-03-07 23:30

Thank You!

Post Reply