Dear Team, I has column. It is key column an...

(Etwo Cargo) #1

Dear Team,

I has column. It is key column and used as ref. Question: how can use it as key, ref and add it multiple in rows?

(Steven Coile) #2

A KEY value cannot occur multiple times within the same KEY column, but can occur any number of times in other columns and in other tables.

(Etwo Cargo) #3

Hımmm Difficult to understand for me. It is sure unique number but sometimes need to put it again. As under unique number part1 (when part 1 arrived) the same number part2. the app is about deliver of the cargo. same unique number but cargo arrived as two part. part1 last week and second part under same unique number today.

(Etwo Cargo) #4

May ı use timestapm as key in different (additional column) column? So, ı can use that unique number as ref and will able to write it several times. Is that will work?

(Steven Coile) #5

It sound to me like the “unique number” identifies an entire order, and that the order may be delivered in parts (hence part1 and part2).

I’d guess the order can be placed initially and parts can be associated with it.

The parts will then be delivered (perhaps at different times) and the delivery needs to be noted.

(Steven Coile) #6

Each order needs its own unique ID, distinct from all other orders. Each part also needs its own unique ID, distinct from all other parts. While no orders will have the same order ID, multiple parts may refer to the same order. In this way, an order ID may occur multiple times in the parts table (but each only once in the orders table). Each part still needs its own distinct/unique part ID, too.

(Steven Coile) #7

So I’d envision a table to capture the orders, possibly called Orders. This table might have the columns: * OrderID: the unique ID that distinguishes the order from all other orders (Text; KEY; REQUIRED; HIDDEN; initial value =UNIQUEID()) * OrderReceived: when the order was placed (DateTime; REQUIRED; initial value =NOW()) * DeliveryAddress: delivery address (Address; REQUIRED) * DeliveryParts [virtual column]: associated parts (list of Ref to Parts[PartID]; app formula =REF_ROWS(“Parts”, “OrderID”)) * DeliveryPromised: expected delivery completion (Date) * DeliveryComplete? [virtual column]: whether all parts have been delivered (app formula =(COUNT(SELECT(Parts[_ROWNUMBER], AND(([OrderID] = [_THISROW].[OrderID]), ISNOTBLANK([DeliveryDate])))) = COUNT(SELECT(Parts[_ROWNUMBER], ([OrderID] = [_THISROW].[OrderID])))))

(Steven Coile) #8

I’d also envision a table to capture the order parts, possibly called Parts. This table might have the columns: * PartID: the ID that distinguishes the part from all other order parts (Text; KEY; REQUIRED; HIDDEN; initial value =UNIQUEID()) * OrderID: the order to which the parts are attached (Ref to Orders[OrderID]; REQUIRED) * DeliveryDate: when the part was delivered (Date)

(Etwo Cargo) #9

+Steve Coile Dear Steve. Thanks for your time. Can i create a column which will give me ID (like 1,2,3,4,5,6…) automatically for each row?

(Steven Coile) #10

@Etwo_Cargo You could, but sequential IDs are risky and best avoided.

(Etwo Cargo) #11

+Steve Coile How can set the column to give number automatically for each new rows?