Using a Table to fill data on a form

[bmayers]

I have a Table with  following that has all the data and new products being added.

i have a form set up to request parts with the following.

i am trying to make it where the user can enter Department, Machine Center, Part Number, Part Desc or scan the barcode to retrieve the data from the table that matches and auto fills in the other ones. 

In a perfect world they could just use the barcode to find the part they are wanting to reorder but not all our parts are in with a barcode and it might be easier to find by department, machine center etc... 

so i have tried to ref the 2 barcode columns from each table and using dereference to the other columns which works great if i only wanted to use the barcode column to filter my data. 

[TeeSee1 · Accepted solution]

Yes, there are limitation, especially UX, to what AppSheet can do.

I have modified the previous app to demonstrate some of the capabilities.

The first sequence is where the barcode is available.

The second sequence is where you see dropdowns getting narrowed down first and then when you see what you do not want, you manually enter values to both request a part and a also update a new part creation.

I am not sure how much more can be done with AppSheet but it is I believe very close to what you are looking for.

Hope you can find a workable solution with your not so PC savvy maintenance people.

 

[TeeSee1]

Not sure if this fits your use case but, using

https://help.appsheet.com/en/articles/2357261-linktoform

you can navigate from your product table to parts request.

That way you can use build-in filters to search the product first and then go to the request table entry form copying whatever you want and additional user inputs.

Just an idea.

[bmayers]

There isnt any way to use a dereference with an if statement? for example if the product barcode is blank allow data input in part number if part number is blank allow input in part desc if nothing returns a match add the new record to the data base and as a requested part? 

Right now the product barcode with dereferencing the product name and product desc works find i can scan a barcode and it will populate the fields with the right info but i am running into parts that do not have a barcode or have not been added to the system and would like for this form to beable to add to the database as well.  

[TeeSee1]

This is one possibility to implement what you want to do or close to it

Since none of the fields besides the product barcode uniquely identifies a product, I believe it is difficult or at least requires very complex expressions in each field.

Instead, I created a virtual column to display matched product candidates based on the values entered in various fields.

Based on the VC, I have two actions

1) If there is a unique product, then you see a button to populate the rest of the fields in the request row

2) If there is no matched products, then you see a button to add a new product row based on the values in the request row.

The expression in the VC is

FILTER("products",
 AND(
  OR(ISBLANK([_THISROW].[category]), [category]=[_THISROW].[category]),
  OR(ISBLANK([_THISROW].[part number]), [part number]=[_THISROW].[part number]),
  OR(ISBLANK([_THISROW].[machine center]), [machine center]=[_THISROW].[machine center]),
  OR(ISBLANK([_THISROW].[department]), [department]=[_THISROW].[department])
 )
)

Action 1 

Action 2 (you should add more conditions such as all the required fields filled)

Hope this gives you an idea to go forward..

[bmayers]

This could be a work around but running into a few issues

What is the formula used here i have tried every which way i could think of and unable to get it to work. 

So i think once i get this formula to work I should be able to input the rest and make it where i can click add request and it prefill out all the corresponding fields. 

Also i was noticing only 1 field would not limit the choices in the other fields. so for example on category if i selected a category the part number,  part desc fields where not limiting their list at all. 

[TeeSee1]

The expressions are..

LOOKUP(ANY([Matched Product]),"products","id","category") <= for category

etc.

However, I modified my previous snippet to make the dropdown lists more responsive to value selection.  (Best viewed in Tablet mode)

https://www.appsheet.com/Template/mobilepreview?appId=91d27401-9337-4c7b-9a40-74b3be859f11

To take a look inside see the sample demo named materials.

https://www.appsheet.com/portfolio/3401559

In this I am using a filter table technique along with a slice to display both in a dashboard. (it is a widely used technique. example here)

This way, you can control the dropdown lists of the filter table columns based on the selection of various columns.

Also the filter table has one row per logon user and this will not affect filter values created by others (you need to first create an entry)

I kept the two actions method used in the previous snippet. However, since these are now based on the Filter table rather than Part Requests, the implementation details are quite different. It would be too long to describe here, so please take a look inside the app.

The reason I chose to use a Filter table rather than staying on the Parts request table is because parts requests rows are created dynamically and could not figure out how to identify the correct row to use as the filter condition of the slice (I may be able to use the last row technique but I am not a big fan of the technique...) . Besides I like the dashboard view better because you can see the filtered rows dynamically (Yes, this is not the case on a small screen smart phone unfortunately).

Anyway it - how you design your app - all depends on your environment.

I hope this will give you more ideas to work with.

Cheers!

P.S.

I am sure others have implemented similar functionality in different ways. I would definitely love to hear them myself!

EDITED:

Maybe not directly related. However, if a user only seeks certain parts based on pre-filters, then USERSETTINGS may be also applied to the slice's filter if you want to optimize performance.

[bmayers]

I really appreciate all your efforts on this but after messing around with it. i believe this is a lost cause. im working with a group of maintenance people that barely know how to turn on a PC let alone trying to walk them thru requesting parts with several steps. i was hoping for an easy way to link the parts database to an order form.

they enter a barcode if they have one, or they enter part number, or they enter part desc, or department that would filter the other fields to narrow down what they are searching for or if it is not in there then they could easily just add the part number etc and request all on the same form but maybe that is impossible with appsheet. i know i can do it in access i just wanted a more mobile cloud based way to do it. 

[Joseph_Seddik]

I've read your posts more than once.

If you can do it with Access you can definitely do it with AppSheet. This is not "a lost case", rather before working on the solution you should first work on your problem definition. 

I recommend that you think about the logic and sequence of actions that would constitute the desired behavior of your app, and write it down in an ordered list in plain language, like this:

1. User will open a form
2. The form will contain the following fields
3. User will fill in X 
4. If A happens, then instead user should fill in Y
5. the App should show B, 
6. User will have an option to fill in Z 
7. The app would respond with C

If you can work out your problem definition and this step-by-step, plain-language flow, you'll get your problem solved, even if you get stuck somewhere others will be better able to help you. Otherwise, you probably won't succeed in solving your problem even in Access.

I had a similar issue with a couple of apps that I had to design for users with little tech knowledge, and this is my best advice to you. 

[bmayers]

I'm sure it can be done in Appsheet I'm just not as familiar with Appsheet as I am with Access. 

1. Parts Database Table (As parts are added from inventory app or from request parts app this list grows)

2. Request Part Data( where the request parts form data goes)

2.Form (user opens app to)

4. The form contains

    A. Product Barcode (If they have a part with barcode scan it and autofills all below data with matching row from Parts Database if Barcode is not in the system then they can add it and fill in all fields below to add the part to both Tables if Part does not have barcode leave blank and proceed filling out the form)

     B. Category (Type of Part Belt,Bearing, Bushing, Sprocket etc) This list will grow as more parts are added to the system. (As with Barcode field this has a drop down that will change depending on if any of the other fields have data in them to help filter down to find the correct part. If no Category it can be left blank.)

    C. Part Number( Not all parts will have a Part number)(As with Barcode and Category field this has a drop down that will change depending on if any of the other fields have data in them to help filter down to find the correct part. If no Part number it can be left blank.)

    D. Part Desc( way to reference a part without a part number or barcode)As with Barcode, Category and Part Number field this has a drop down that will change depending on if any of the other fields have data in them to help filter down to find the correct part. If no Part Desc it can be left blank.)

    E. Department (Enum  with  Dynamic Department from Parts Database that will filter the fields to return only parts with match from Machine Center Column.)

    F. Machine Center (Enum with Dynamic Machine Centers from Parts Database that will filter the fields to return only parts with match from Machine Center Column.)

   G.  PO  # (Auto Generated using CONCATENATE("TJ" &TEXT(TODAY(),"MMDDYY"),Right([_RowNumber] -1,5)"001")

   H. Qty (How many needed)

    I. Importance (Enum Critical, High, Medium, Low

    J. Additional Notes

Example: 

If i scan a barcode and matches a barcode in the Parts Database then the Row will fill in the Department, Machine Center, Category, Part Number, Part Desc. The user will then fill in the Qty Importance Requested By and Additional info and save creating a row in the Requested Parts  

If the barcode does not match then user fills in Department Machine Center Category Part number Part Desc Qty Importance Requested By and Additional info saves create a row in the Requested Parts and Creates a new Part in the Parts Database with Product Barcode, Department Machine Center Category Part Number Part Desc from what user had entered.

If the Part has no barcode then the user can use Department, Machine Center, Category, Part Number, or Part Desc. to find the part they are needing to order. Department they enter Dept1 and it will filter down all the other fields to only show matching data that = Dept1 if they select Machine1 then the remaining fields will filter down to match only if they = Dept1 and Machine1 and so on. 

after filtering down Department, Machine Center, Category, Part Number, Part Desc and no match then they fill in the QTY, Importance, Requested By and Additional info and it will add a row to Requested Parts and Parts Database. 

This away the Parts Database will always be growing and next time they request that part it will be there. 

the Requested Parts will then go thru the approval system built already on another app. 

[TeeSee1 · Accepted solution]

Yes, there are limitation, especially UX, to what AppSheet can do.

I have modified the previous app to demonstrate some of the capabilities.

The first sequence is where the barcode is available.

The second sequence is where you see dropdowns getting narrowed down first and then when you see what you do not want, you manually enter values to both request a part and a also update a new part creation.

I am not sure how much more can be done with AppSheet but it is I believe very close to what you are looking for.

Hope you can find a workable solution with your not so PC savvy maintenance people.

 

[bmayers]

https://www.appsheet.com/Template/AppDef?appName=RequestParts-1089219

Maybe this will allow you to open the app i started. 

I can get one field to work great so in above app the Product Barcode field  does everything i want it to do but trying to get the Part Number, Part Desc, Category, Department and Machine Center fields to do the same thing. But the only way i can get those fields to work right is by using the [{ref_column}].[{lookup_column}] when using it they are hidden until something is put in the Product barcode and i would like for all the fields to do exactly what the product barcode does here. 

 

[TeeSee1]

The problem with this is that columns that have an app formula is not USER editable - Automatically computed.

Another limitation using a Form is that you cannot use actions.

That is why I am using a Detail view with quick edit.

What could be done is to use the column name. Not very attractive unfortunately.

[NotARobot_Mark_]

Your app link. Ones up as not found

[bmayers]

i think i fixed the link where you can look at it now.  

So if there is no way to make the columns Editable if first condition is false? for instance the [{ref_column}].[{lookup_column}] cant be used in the valid if instead of the app formula with edit_if and Show_if possibly? I havent been able to figure out how to get it to work and maybe you cant.  

im also thinking i guess on the google sheet side i can make 5 tabs each using a different ref then make 5 form views using each ref as the main selection then adding them to a dashboard view with just the primary column. They select the part they need and save it. It goes to google sheet in 1 - 5 tabs depending on which column they used. On  google sheet I can just use arrayformula query to take the 5 tabs into the 1 Request Parts tab. 

[NotARobot_Mark_]

Nope, now it just goes to the template page.

[bmayers]

https://www.appsheet.com/start/7237d264-6d00-4223-8faa-e2be56dd160c

heck i dont know lol try that. 

[TeeSee1]

When you have the app formula filled, the field is indeed editable (the value changes), not just by user. The system calculates the value based on the formula you give.

A question. What do you want show in the rest of the fields, when a user, let's say, selects a machine center? There could be many possibilities I assume. If you have 100 parts assigned to a particular machine center, what and how you want to display that in a little box? AppSheet's dropdown is exactly the solution for this. You can search the dropdown list because the space is limited for display.

Your idea of creating 5 sheets with 5 forms will not work. Or won't solve the issue above. 

Maybe I still do not fully understand what you want to accomplish

[bmayers]

Yea the 6 main columns.

Product Barcode

Category

Department

Machine Center

Part Number

Part Desc

i want all to work exactly how the product barcode field works. when it is the ref to the table..

i enter a barcode it matches and fills in the other 5 rows with that row data.

so if they put in Machine Center and select Machine1 then the other 5 categorys will have the drop down list filtered to only have the matching rows

if they then pick a category it will filter down the other columns even more.

The user filter options does this if you use dashboard view have a filter set up with those 6 options you can narrow it down then, maybe i could just have the selected row populated to the form view. 

[TeeSee1]

so if they put in Machine Center and select Machine1 then the other 5 categorys will have the drop down list filtered to only have the matching rows

=> this is exactly how my demo app works. All the progressively filtered values are dynamically reflected in the dropdown list of every field.

You just cannot show the values in the field boxes but a single hit or no hits are indicated by different Action icons. Each does exactly what it supposed to do - one that creates a part request, and the other create a part request as well as a request to add the new part.

[bmayers]

The Materials Demo app didn't allow me to filter anything. the select Part View is blank, the parts requests view has 3 items and an add button that goes to a blank page, Parts View also just has a table then the add button takes you to a blank form with no filter, the New Part Creation Request is the same table with add button to  blank form no filter. So i was confused on how it worked exactly. 

[TeeSee1]

The Filters table has USEREMAIL() specific rows. So you need to create a row with whatever email login you are using.

I suggest you just copy the app, enter your own data and try.

[bmayers]

Thank you I got it to work where they can use a user filter and table of database on a dashboard view like you showed and add it to a request form. Now im just trying to figure out how to make it where if they scan a barcode on the user filter thats on dashboard view if the barcode doesnt exist it just has a blank page and they have to hit the x at the top right. Can we make it where if they scan a barcode and results are "" it reset the dashboard View back to Blank? See Pics below. If not thats not a big deal just would like for it to. 

[TeeSee1 · Accepted solution]

The system search field affects both the Filter and the Parts views.

Therefore, when you enter a value that does not exist in the views, they obviously show nothing.

You should display the barcode search field in the filter view and let your users use that.

[bmayers]

Thank you for all your Help! It works great now moving on to the next step!

[benjib]

I'd love to know, how did you put that form in the dashboard?

[bmayers]

Its a detailed view with quick edit columns. its used to filter the data on the table. 

[Joseph_Seddik]

OK, how will this auto-fill would work? If a user for example chooses a Category, does this category include only one product so that it can automatically be filled? The same goes for Department and Machine Center for example.

Also, please post screen shots of the tables you currently have. Is there a Category table? Department table? etc. What are the reference columns you have in all of your tables? 

Thanks.

_____

P.S. It is not about Access vs. AppSheet. Perhaps I'm wrong, but I still think the problem definition is not complete. Otherwise, if you know how to solve it in Access, you can also tell us how pls and I would help with the translation to AppSheet.

[bmayers]

I agree im not saying access is better or worst and im definitely  no good at either. I watch you tube videos and read forums to find solutions on how to do what i am trying to achieve and currently i have spent hours on youtube trying to find anything remotely close to what i am trying to do here and havent had any luck so thought i would ask for help. Shoot im even willing to pay someone to help but havent had any luck finding someone . I feel like what i am trying to do here would be really simple for appsheet to do. i just dont know how to explain it i guess.

[Joseph_Seddik]

I'd suggest you get in contact with @TeeSee1 who has already worked on some ideas for your app.

If he's not available to help you, feel free to contact me: ss.douxjours@gmail.com  

[TeeSee1]

I am not into consultancy.

So if someone needs extensive help/support, then please seek someone else.