Request product app inventory

Hi im trying to build the following app for my technicians:

  1. they should be able to search or look up all products in inventory (mysql db) i already have that query.
  2. in the app technician should be able to request products they need for the installation (im a internet provider in my country)
  3. when they request the products they need, all should be saved in a tmp table may be mysql too.
  4. in the internal desktop app, a member of my team should be able to see all requests per technician and open each one, when open one should see all details and a button to approve or deny the request, when approved is clicked should save that info to another table. If press deny request, should send and email to technician and a notification to his mobile.

whats the best approach for this? knowing i got like 5k products i can show them as a cardview or listview, should be like a select with search option and add them like a shopping cart.

thank you

for my desktop app i have something like this

like shopping cart using localstorage and some js but not sure whats the best option for the mobile app , should be easy for the technician

hi @Kabirdas any news?

Hey @agaitan026!

If you're working on Retool Mobile you might try using one of the Collection components (docs here). Using server-side pagination with limit offset along with your search might also help to limit the number of records returned to something that fits more neatly within the mobile app.

Temporary tables are only accessible within the current session so you'd likely run into issues when trying to access the same data from a desktop app, or possibly even within the same app due to how connection pooling works! It might be better to have a fixed table that stores technician requests along with an identifier for the technician and the customer.

What do you think? Does that sound doable?

1 Like

Yeah I will try that's but for the collection it's strictly required to show that ? In the first screen I would to show just the Search field, after I type something it should auto display product and should have a button to add to the request then when technician finish adding products should press request button to save to the temp table. Or how you recommend me to do this?

Tech may add one or more products to the request before sending to db

something like this but when i type in the search field it should display then the products, so the collection view should be hidden, then after i click on add product should add to the request (like shopping cart) then i open the request will all the products i added then i press Request button and then save that info to a DB . is that possible? thanks

pd: each product should have option to use like stepper component so tech may request 1 or more products just like a shopping cart

That looks very possible! I imagine you can use a fairly similar pattern to the desktop version where you might store the selected items in a temp state. If you'd like for the technician to pick a quantity of items to add you might try opening a modal or using a custom collection for some more customizability.

I don't know of any app that has been built with this exact configuration but if you run into any trouble building it out let me know!

1 Like

but for the first part what you recommend? how i create a search field and when im typing to display products found in the bottom? any mockup ? and also on what is the best way for technicias to add products to the product request #XXX

If you're querying MySQL you should be able to use a pattern like the one mentioned here to get the search functionality. Again, depending on how many records you're expecting to be returned you might want to add a LIMIT clause to your query. You might also consider not triggering the query automatically but using a "Change" event handler as well with some added conditions (docs) like only searching once the input is at least a certain length, or adding a debounce to the handler so that it doesn't run too often.

Is that helpful?

For adding products to a certain request number it might be good to store that in a temp state as well. That way you can reference it when you run the insert query for the product request. How is the request number generated? Is it automatic?

1 Like

but im struggling on how hide the collectionview at the beginning and then auto show the collection once the search field finds something

Ahh I see, have you tried something like this in the collectionview settings?

If you're using that, you can also use an event handler that resets the query data in order to hide the collectionview again after you've gotten some results:

1 Like

hi @Kabirdas i got that

but still showing the collectionview

and i got this

the ilike is not supported in mysql

can you help me with my example? i can give you access to the test project

Good call on ILIKE not being supported! Sorry I had missed that, if you want to use case-insensitive matching you might try using LOWER and LIKE similar to how they do in this thread.

As far as the container not being hidden - what is at the moment?

1 Like

just have this

Got it, in that case has something in it so the container should appear. You can try creating a separate JavaScript query that does query1.reset() to try clearing it. Or, you can use the Reset event handler from the image above. Do either of those work?

1 Like

but how you reset when i stop typing on the search field? any change you can help me with my app directly giving you the url?

the query reset doesnt do anything.

ok i tried adding ; and works but how i configure when i erase the text field to hide the collectionview?

i tried this

but doesnt works

I see, how about using the following for your query instead?

select * from products where {{!!textInput1.value}} and name LIKE {{'%' + textInput1.value + '%'}};

That way the query won't return anything if there's nothing in the text input which should collapse the collection view.

Does that work? If so, you may not need to use query1.reset() or the hidden field at all.

1 Like

yes it works