0

Join on non-key field to update records

2nd try. I can't figure this out, but I think it should be simple.

 

ITEM Import Table:  Item ID, Available quantity, Import Timestamp.

Target Table:  Item: Item ID (not Ninxo ID but parent system id. parent system does not have Ninox ID), Available Quantity.

how do I join on Item ID and update the ITEM table with what quantity passed to Ninox in import table record?

 

[ITEM IMPORT TABLE]  -->  [ITEM TABLE]

 

ITEM ID                              ITEM ID

AVAILABLE QUANTITY  

IMPORT TIMESTAMP

7 replies

null
    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    [ITEM IMPORT TABLE]  -->            [ITEM TABLE]

                                                     NINOX ID

    ITEM ID                       JOIN        ITEM ID

    AVAILABLE QUANTITY  UPDATE    AVAILABLE QUANTITY

    IMPORT TIMESTAMP

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi

     

    When you talk about the parent system, you mean a system outside of Ninox, and that this has it's own set of IDs.

     

    Does the Import table just hold the latest import, or does it retain previous imports?

     

    Regards

     

    John

    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    Parent as in a business system outside of Ninox that has it's own set of IDs. It generates an Item ID as in 6-111467 (Alpha-######). I hold this ID in Ninox, as when I pass it downstream I must pass it along. I flush the Import table each cycle. Otherwise, since the list of availability may change cycle over cycle, I'd end up with orphan records. I still use a 'max' on the queries I've tried to ensure pulling the latest date.

    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    Input file ooks like:

    Item Code,Avl/Per
    2-100035,1025
    3-100050,1145
    3-100065,0
    3-111006,168
    4-100380,0
    4-100382,0
    4-100395,0
    4-111063,125
    4-111067,234
    4-111080,0
    4-111081,0
    4-111140,1099

    • Fred
    • 3 yrs ago
    • Reported - view

    Maybe this could work.

     

    On a button I did a simple update of the current record number field updating the number field of a non-related table. Here is the script updated with your info.

     

    let curRec := this;
    let xUpdate := first(select Item where 'Item ID' = curRec.'Item ID');<--Ninox assumes that it could return multiple records that match so just grab the first one since you know there will only be one. Unless you can get many records in Item that has the same ID then you need to add more qualifiers.
    xUpdate.('Available Quantity' := curRec.'Available Quantity')

     

    From there you can keep adding fields to be updated.

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi

     

    If your Item Code is called Ref, and quanitiy called Qty in both your Products and Import tables and you have a flag called Flag that notes new imports vs previous imports then this code will loop through the import, update the products, set a relation between them so you call look back at all imports on a product, and set the flag. If the import has a product that it can't find than it will create one. So your code may be more simple than this if this is more than you need.

     

    for a in select Import where Flag = 0 do
       let b := a.Ref;
       let c := a.Qty;
       let d := first(select Products where Ref = b);
       if d = null then
          let e := (create Products);
          e.(Ref := b);
          e.(Qty := c);
          a.(ProductID := e)
       else
          d.(Qty := c);
          a.(ProductID := d)
       end;
    a.(Flag := 1)
    end

     

    Set your import table up so that the default value for Flag is 0

     

    Regards John

    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    Thank you Fred and John. I will experiment with both. I appreciate it!

Login to reply

Content aside

  • 3 yrs agoLast active
  • 7Replies
  • 472Views