My aim was to use a select query to select fr...

(Park Real Estate App) #1

My aim was to use a select query to select from a list where a value was equal to a value in another column as well as add the option to create a “New Entry”, similar to an enum list. Then autofill some following columns. Since this is not possible what is the best workaround?

At the moment I have a select query that is the list and a “New Entry” as below.

=Select(TableA[Column1], [Column2] = [_ThisRow].[Column3]) + {“New Entry”} This also helps when an existing record is accidentally chosen and the user wants to go back to create a new record without doing the form again.

Then I have a group of columns for a new entry to manually fill in when "New entry is selected and another group of columns of the same value types for the autofill if a record exists and selected. Dependent if a new record or existing record is chosen the alternate group of columns are then hidden.

Although what is in place is functional. The problem is the complexity it is causing and the displayed data is inconsistent. For example in a related record inline view, "New Entry is displayed instead of a Name though I can not refer to the other column to show the correct name. Also existing entries need to be referred from another table and new entries need to be referred from the current table which now limits my functionality for future development improvements.

(Aleksi Alkio) #2

Are you able to send some printscreen about your issue?

(Park Real Estate App) #3

Thanks Aleksi. Please see below. The app is currently in dev stage so Im using dummy data.

(Park Real Estate App) #4

Above is a new entry and below is an existing entry.

(Park Real Estate App) #5

Below is the 2 groups of columns

(Park Real Estate App) #6

As you can see the last related entry has a blank name due to the entry being an existing name which is another column.

(Praveen Seshadri (AppSheet)) #7

@Park_Real_Estate_App, instead of having a flat table design like this, you might be better off with an Item-Detail design. So you really have a reference to another table and you get the option of adding a new entry to that referenced table via the dropdown (“New” is one of the options that shows up automatically in the dropdown if the referenced table allows new rows to be added).

(Park Real Estate App) #8

Thanks Praveen. Is there any links or resources that can show me an example? I am currently referencing on another table using any(select …though obviously not the way to go.

(Praveen Seshadri (AppSheet)) #9

Sure, this article may help. - The “Item-Detail” Data Pattern The “Item-Detail” Data Pattern

(Park Real Estate App) #10

I Believe this is what I am doing. Below is the structure of the database in summary consisting of 3 tables. Each of the 1st 2 tables connects via the address and pulls down the data. The diagram just does not show that the customer data pulls down to the properties table as well. Thank you for your patience and feedback.