Problems with formula

Hi,

Im Jimmy, nice to meet everybody. Ive been trying to develop an app to help my business, but i ran into some problems. I will try to describe it as best as i can and i would really appreciate some input from someone more knowledgeable.
My tables are the following :
Customers(CustomerID ;First Name ;… ;CustomerAddress1)
Products(ProductID ;Brand ;… ;Qty)
Orders(OrderID ;Date ;CustomerID ;ProductID ;DeliveryID ;Status)
OrderDetails(OrderDetailsID ;OrderID ;ProductID ;QtyOut ;PricePerUnit)
Deliveries(DeliveryID ;DeliveryAddress ;DeliveryCost; DeliveryDate; DeliveryStatus)

I am trying to create a map view of deliveries. The problem at first was that deliveries didnt have any address columns on which i could base my map view on. I added a virtual column GetCustomerID with the following formula, to retrieve customer id based on delivery id from Orders table : LOOKUP([_THISROW].[Delivery id], Orders, Delivery id, Customer id)

I then added a DeliveryAddress column with the initial value being set to retrieve CustomerAddress1 from Customers table based on value from GetCustomerID column.
LOOKUP([_THISROW].[GetCustomerID], Customers, Customer id, CustomerAddress1)

The problem is that the formula does not work for CustomerAddress1 giving the following error :
Unable to find column 'Customer Address 1’
But it works fine with any other column as input : FirstName, LastName etc.

This has been driving me crazy for the past 2 days. If anybody could help me get out of this hole.

Why isnt the formula working for the address column but works for any other type of column ?
Maybe im going about it the wrong way and should not have another table just for deliveries ?

Thanks for taking the time to read through all my non-sense.

Cheers

Hi @B18ANU,

Welcome to the AppSheet community.

This may not be final solution to your issue, but you may wish to check following. You have written the error as

There seems to be a space between “Customer” and " Address" and “1”

In all your other furnished details, the customer address column name seems to be with no space between Customer, Address and 1. You may wish to check if it is typo in this community post message or have you mistyped the column name somewhere in an app expression.

2 Likes

Hi,

Thank you for your suggestion, i have tried changing the name of the column multiple times in the past but with no success, now i have tried it again at it seems to work. Thank you for your help Mr. Suvrutt_Gurjar.

2 Likes

Thanks for update. Good to know you got it working.

So even if that fixed the formula working in test mode, it wont actually autocomplete the cell in order form view or even after i`ve submited a new order, the cell remaining empty at all times even if the formula for inital value works in test mode.

I`m trying to leave the field editable in case a recurring customer doesnt want the item at the same address as the one submited in the Customers table.

Any thoughts on this ?

Could you please mention on the below?

How is DeliveryID set in the Orders table?

Is Orders table child table of Deliveries table? Are Customer ID, Delivery ID reference columns in the Orders table?

In table Deliveries i have Delivery ID set to ref and is part of Orders. In the Orders table the Delivery id has an initial value of UNIQUEID() and is non-editable so that each time i create an order a Delivery id is automatically attributed to it.

Le : forgot to mention that Customer ID in Orders table is set to ref

Thank you. Sorry for my late response as different time zones result in different working hours.

I believe you could pull delivery Address in Deliveris table in two stages.

Please pull it first in Orders table with dereference expression [CustomerID].[CustomerAddress1] , say in a column called [Customer Address in Orders Table]

Now pull this address column in Orders table through another dereference expression in initial value of DeliveryAddres column in the Deliveries table with an expression

[DeliveryID].[Customer Address in Orders Table]