Relating records with a multi column key

[Martina_Hawkins]

I have a table called Orders and the key is Order No + Delivery Number so a Computed Key is generated CONCATENATE([Order No],": ",[Delivery No]).

I have a table called Order Details which has a key called Request No.

An Order can have multiple Requests.

I am struggling with relating the 2 tables. I was able to do this when Order No was the key but now I have a multi column key and not sure what columns I need in Order Details to link the 2 tables.

[1minManager]

In simple terms all you show need is:

Order Table
[OrderID] = TEXT , Initial_Value=UniqueID()
[Order Details]
…

OrderDetails Table
[Order] = REF
[OrderDetailsID] = TEXT , Initial_Value=UniqueID()

[Details]
…

Without seeing exactly how you’ve got it setup, trying changing you App to be like the above then remove and re-add the OrderDetails table as that creates an automatic Ref. Probably easier and quicker than trying to fix it manually, until you get more experienced with Appsheet

Simon@1minManager.com

[Martina_Hawkins]

My original setup was as follows which worked fine

Orders
[Ordered Id] - Key
Order Details - ref
…

Order Details
[Request No]
[Order Id]
…

I now need a second key as an Order can be split into 2 deliveries.

E.g Order No 100 with 10 requests arrives short with only 5 items so another Order is needed but it needs to keep the same Order No for the Accounts dept so when the order is first created it now has a delivery No to make it unique

Order No Del No Qty Ordered
100 1 10
100 2 5
200 1 20

Orders
[Order Id] -Key
[Delivery No] - Key
?? Ref
…

Order Details
[Request No] - Key
[Order No]
[Del No]
???

[1minManager]

So here’s what I’d do. Firstly I’m going to suggest renaming the second table to Item since that clearly indicates each row is one item

Order
[OrderID]
[Order Number]

Items
[Order]=REF
[ItemID]
[Item]
[Amount]

Deliveries
[Order]=REF
[DeliveryID]
[Delivery No]
[Date]
[Item]
[Amount]

They need linking like this

    Order
       |
 -----------
 |            |

Item * * * * * * * Deliveries

ignore the *'s its just to get the formatting right

So to keep a running tally you now just create a formula that takes the Item total and deducts off all deliveries

Item[Remaining] = [Amount] - SUM(Select(Deliveries[Amount],[Order]=[_ThisRow].[Order]))

Hope this helps

Simon@1minManager.com

[Martina_Hawkins]

Thanks, don’t think this will work for us, our processes are slightly more complicated but I think I have a solution that I am testing out.