Help to setup database

Im fiddling a little with a new project and trying to learn Ragic at the same time.

I want to setup a shipping system.
It should contain tables:
Sales order (SO nr, Customer ID)
Shipment (Shipment ID, First loading, Final delivery)
Shipment order (Shipment order ID, Shipment ID, First loading, Final delivery, Loading, Delivery)
One sales order can contain many shipments.
One shipment can travel in many shipment orders ( for example if shipment is from A-C, we can have two shipment orders - one A-B and one A-C)

This is fairly easy to setup

But now to what gives me headache: SHIPMENT DETAILS
Shipment details should be in subtable format and should be visible in both shipment and shipment order sheet (same records should be visible). If I make it a subtable in Shipment sheet I can not link it in in Shipment Order sheet because both Shipment order and Shipment details is a child of Shipment
Any suggestion how to do this correct or does it require some jscript?

Or maybe setup should be like this
Sales orders → Shipments → Shipment details
Fairly easy.

But then i want to have shipment orders where a shipment can travel in many shipment orders.
Problem is I want to make the shipment record first and then when routing is decided make correct amount of shipping orders so shipping orders can not be a parent of shipments. Both shipment order and shipment details should be a child from shipments, but on each shipment order i want to include the shipment details from the actual shipment

Regarding “Shipment details”, you said that it should appear in both “Shipment” and “Shipment Order”, and it seems that Shipments can be separated into different Shipment Orders.

Does “Shipment details” get input based on “Shipment” or “Shipment Order”?
Scenario 1:
If “shipment deteails” is “shipment order-dependent”. then it’s possible to have shipment details as subtable in both “shipment” and “shipment order” forms.
For example:
Shipment detail (Shipment ID, Shipment order ID, details)
You can display Shipment details as subtable in both shipment and shipment order by using “Show references from existing sheets” on both shipment and shipment order form.

Scenario 2:
However, if “shipment details” is “shipment-dependent”, that would mean shipment details doesn’t have a key to map to “shipment orders”.

For the tricky “Scenario 2”, a way to show “shipment” details in “shipment order” is to aggregate all the “details” into a single “text field” on “shipment”. And using Link and Load to show it as a linked field instead of a whole subtable entries.

Scenario 2 is applicable.
First make the order.
Then shipment (with shipment details)
Then put the shipment in one or several shipment orders.

Then thing is that I need the details in fields on the shipment order as the info from there will be forwarded to other system/reports/loadinstructions.

The text field solution is a solution, but not the one I want - mainly because the shipment details can contain several records for one shipment

An idea would be to add a shipment order field in the shipment details table and somehow make that autopopulate itself with shipment order nr when I make a new shipment order via a button in the shipment sheet. Would that be a solution?

The main issue with scenario 2 is that “shipment details” only has “shipment ID” as key.
However, for one shipment ID, it will appear in multiple “shipment order”.
Such that MULTIPLE “shipment order ID” need to be matched to each “shipment detail” records.

I manage to get something working by using “multiple select” for the Shipment Order ID under “Shipment Detail”. However, automatically insert all the shipment order IDs to Shipment Detail requires some JavaScript post-workflow, otherwise you will need to do it manually since that Multi-select field does not support “formula” as input. (Sample JavaScript Code included below for reference.)

See if it helps.


Sample Post Workflow

var rootNodeId = param.getNewNodeId("1027076");
var query = db.getAPIQuery("/devtest/34");
var entry = query.getAPIEntry(rootNodeId);

var order_list_text = entry.getFieldValue("1027090");
var subtableSize = entry.getSubtableSize("1027087");

for(var i=0;i<subtableSize;i++){
  entry.setSubtableFieldValue("1027086", entry.getSubtableRootNodeId(1027087,i), order_list_text)


 * This comment is auto generated on 2024/05/14 23:00:00, and it reflects the field definition at this given time.
 * If you need the current field definitions for your database, please go to "Start" => "Account Setup" => "DB Maintenance" => "Download Data Dictionary"

 * AP_Name:xxxxx
 * Key Field: 1027076

 * shipment order ID subtable key: 1027083

 * Shipment Detail ID subtable key: 1027087

 * Field Name                    Field Id
 * - - - - - - - - - - - --------
 * shipment ID                  : 1027074
 * First Loading                : 1027075
 * Final Delivery               : 1027088
 * [text] All Shipment Orders   : 1027090
 * shipment order ID            : 1027077
 * First Loading                : 1027079
 * Final Delivery               : 1027080
 * Loading                      : 1027081
 * Delivery                     : 1027082
 * Shipment Detail ID           : 1027084
 * Shipment Order ID            : 1027086
 * Remark                       : 1027089

1 Like

Wow! This is great stuff. Thank you very much - will try it out