Multiple issues: Ref, autofill, and logical branching

I used Google Forms to create my original app. (I don’t want to reconstruct it.)

I have workers that have to do 1 of 2 tasks at a machine: either check for coolant and record level OR go through a checklist of physical items.

As it existed, they typed the machine number. Next, they selected the type of machine. Recorded the refract number. Submit. Depending on type of machine (Dial, CNC, Special) brought them to part 2: their checklist. (Checklists for each type of machine differs.) Logical branching worked great.

Now come the errors:
I want to idiot-proof the app.

  1. Operators would type in incorrect machine numbers. (Bad) OR
  2. They would select the wrong type of machine bringing up the wrong checklist.

So, I changed the machine number input to Ref (I gave them a list of machines to pick from).
Then I changed the MACHINE TYPE to ref. This way it prefilled the type of machine depending on the number of the machine you chose.
BUT… Now the logical branching has disappeared. If you Select 236, it preselects Dial (as it should). But, I can only SAVE from this point forward. (Previously, I had NEXT which would take you to the proper checklist).
I don’t know how to get the NEXT button and logical branching back.

Well, I got the machine numbers to work via Ref.
But, I had to switch back to EnumList for the machine type to get the app and logical branching working again.
It still leaves the potential for someone to choose the wrong machine type.

I will leave it as is until I can find a way to use Machine Number to preselect Machine Type AND keep logical branching.

Still need help to do this. Any ideas, please?

If you already have it setup in a way that tells you exactly what type of machine each number is, you should try just making it a regular text column that uses a VALID_IF to ensure its one of the correct types. And then I would use the LOOKUP function in either Initial Value or App Formula depending on your wants and needs. As far as your logical branching in the form, I’ve not really messed with it too much, but I’ve never heard of it stop working.

So my suggestion is to show us screenshots of your overall table structure (for this form and any tables it references), the column details of all the ones you mentioned in the first post, and anything on how you are using the logical branching. That should get any of us the details we need to figure it all out.

Screenshot of original data in Google Sheets

Screenshot of spreadsheet containing Machine Number and its type (Dial, CNC, Specialty)

Screenshot of main Table in the app

I think that is what you need for data and structure.

Now the logical branching.

Here screenshots as you progress through the app (specifically that which pertains to my changes desired.)

I currently have the Machine Number (line 8 in the FormResponses1 table) as a Ref. This is working properly.
I would like Type of Machine (line 10) (now Enum) to prefill with the type of machine.
AND THEN be able to move forward in the logical branching to the checklists (which are based upon the type of machine).

When I changed “Type of Machine” yesterday to Ref (referencing the 2nd column of the data), it pre-filled, however I could no longer move forward (via NEXT button), I could only SAVE.

I honestly don’t know HOW appsheet builds in actions to make logical branching. I couldn’t figure it out, so I created a Google Form first which allows logical branching and let Appsheet do the rest.

For auditing purposes, it is vital that our users don’t make the mistakes I spoke of (wrong machine type with machine number…and therefore a lack of proper checklisting.)

That is my main concern. My secondary concern is how to get a list of machines appear daily and disappear as they are completed. I can’t even think of the logic to make it happen. Maybe some type of slice that compares Work Done Today slice and Work Left slice. If 108 is on work done AND on work left, then DO NOT DISPLAY it on Work Left??

Your Valid_If works great. It does what I need. Weird glitch though: depending on the machine type picked, the display is different. Screenshots to demonstrate what I mean.

If you choose a number that is a Dial machine

If you choose a number that is a CNC

If you choose a number that is a Specialty Machine (suddenly interface changes)


Major issue? No. It does what I need it to do. Glitchy? Yep.

The logical branching works properly after. YAY!

I was not expecting that essentially just fixing it. As for that last little issue, open that [Type of Machine] column settings and look for a setting that refers to how it is displayed. It’s probably auto, and auto will sometimes be weird. Try setting it straight up to one of the other options and pick what you like the best.

I almost forgot about this part. For that, you’ll need a date last completed column in your machine listing. And then yeah, a slice would work just fine. OR(TODAY()>[Last Completed], ISBLANK([Last Completed])
That should filter out anything already done for today and ensure new machines that haven’t been inspected yet show up as well. This is assuming every single machine needs to be checked every single day.

Sounds great…EXCEPT there is no “how it is displayed” control. I know what you want me looking for. Just doesn’t exist. If it was ENUM, I can control that.

So, I would need a column (or virtual one) on my Machines list? That would have to me some sort of expression with MAXROW and TIMESTAMP from the master data, right?

You could write an expression that searches the master data for the most recent DATE (we don’t want time in this case), or you could try to write a workflow for the master table that will update the date for a machine as the data gets entered.

That’s right. I forgot we just changed that when I was looking at the pictures again. Hmm, theoretically I don’t think AppSheet is supposed to flip between those two displays from the Valid_If. What did you put for the Valid_If, out of curiosity?

I think this is what you need…

Well that looks exactly how I do it, so I’m not sure why its switching styles.

Yeah. Weird behavior. Not MAJOR because it works. LOL

I will try some expressions for last date over the weekend.

In the meantime, I have created a slice that gives a running count. i.e. 13 CNCs (done today) and 6 Dials, etc. I could add another column to the slice that takes the total and subtracts the count. As in, 13 done and 40 to go.