Referencing a Column containing both text and numbers

[Jay_Bang]

Hi. New here.

I have successfully referenced a column in a data table.

However, when I use that reference as a drop down in another table, it tells me the input is invalid. I assume this is due to the fact that the reference has numbers and text in it, which does not “compute”.

How do I make the ref table accept both numbers and text?

Thanks

[Steve]

What is the exact text of the error message?

[Jay_Bang]

Hi there,

Thanks for the quick response. See attached image.

[Jay_Bang]

Second screenshot showing me trying to reference now and it picks up the values, but the reference remains “invalid” it doesn’t show the fields Production Date and Production Total

[Steve]

Could you provide a screenshot (or shots) of the configuration screen for the Batch Number column?

[Jay_Bang]

Thanks Steve, really appreciate the assistance here.

[Jay_Bang]

Hope this is what you require. Sorry, still new to the forum.

[Jay_Bang]

You may need this one as well. This is the ManagerInput Table. Everything else is in the WorkerInput Table.

[Steve]

Thanks for all the screenshots!

The problem is pretty straightforward: your Ref column (Batch Number:) doesn’t contain a Ref value.

A Ref value is used to locate and access (“reference”) a row in another (or even the same) table. Every row in a table has a key value. Each row’s key value is different from every other key value in the same table (i.e., is unique); no two rows in a single table can have the same key value.

A row’s key value is contained in its key column. The key column is determined by the KEY? toggle in the column list of the app editor, which is also the Key property within the column’s individual configuration screen.

Every table must have a key column. A table can only have one key column. Every row in the table must have a key value.

A Ref value is the key value for a row in another table. The table that key value belongs to is identified by the Source table property of the Ref column. In your case, your Ref-type column is Batch Number: in the ManagerInput table. The column has a Source table property of WorkerInput, indicating the column will contain key values for rows in the WorkerInput table.

The breakdown occurs with the Valid If expression you have for the Batch Number: column: WorkerInput[batch number:]. This expressions tells AppSheet to present and allow the user to select from a list of values brom the batch number: column of the WorkerInput table. This is a problem because the batch number: column of the WorkerInput table is not that table’s key column. The key column for the WorkerInput table is the Timestamp column.

So you’re trying to use a non-key value as a Ref value, which is invalid.

What are you trying to accomplish in asking the user to choose a batch number? How would you then like to use that chosen batch number?

[Jay_Bang]

Hi Steve,

Thank you for the detailed response. I really value your input and guidance here.

To answer your question.

Within the WorkerInput table, users input information such as batch number, production totals and then parameters associated to that batch number (protein, moisture) etc.

The Manager then must be able to select from created batches and automatically see all the parameters entered in by the workers (production totals, protein, moisture) etc. He then will have the ability to over-ride or approve.

Hope this helps?

Jason

[Steve]

Will a batch number only ever occur once in the WorkerInput table?

[Jay_Bang]

Hi Steve,

Sorry for the delay. Yes, batch numbers are unique and get generated by “workers” for a production run.

[Steve]

The easiest fix would be to change the label column of the WorkerInput table from Name: to Batch Number:. The row’s label column value is what is displayed to the user instead of the row’s key column value when a Ref value is used.

Also, why is the type of the Batch Number: column set to Address?

[Jay_Bang]

Hi Steve,

I found that there were issues when users were trying to input numbers into a text type or text into a numbers type. I dont want an Enum List because it remembers everything and I don’t know how to remove entries at a later stage. The best thing I could come up with (as mentioned before, new to this) was to use an address type because it accepted both text and numbers.

[Steve]

Jay_Bang:

there were issues when users were trying to input numbers into a text type

Can you provide more details?

[Jay_Bang]

Steve:

u

Silly mistake on my part actually. I just tested the text field with numbers and text and it did not give me an error. Not too sure why I did that actually.

Apologies for wasting your time on that Steve.

Jason

[Steve]

Jay_Bang:

Apologies for wasting your time on that Steve.

No worries!

[Jay_Bang]

Hi Steve,

So Iv’e done all that you mentioned around creating labels, and it now gives the correct batch number display. I cannot thank you enough for that. Now, how do I now reference all worker input information into manager input fields so that he can view or edit?

I.E. Manager selects a batch from available batches - all corresponding fields from WorkerInput (Production Date, Production Total) needs to populate over in the manager view with manager being able to edit or just save (approve).

Is that possible?

[Steve]

That you want the manager to have the ability to edit values makes your approach difficult. Have you considered just having the manager review the WorkerInput row directly?

[Jay_Bang]

I have not given that much thought. I have seen when I make the WorkerInput a read only, then I can reference all I want - but it cannot be edited.

I am not deterred from your suggestion, I just don’t know how that would actually work. So essentially he can view all records and make adjustments as he sees fit.

Ill give that some thought and then open a new query should I get stuck there - I think you have answered all questions for now.

Really appreciate your assistance.

Jason