Writing a value into a linked table field

How can I write a value into the linked table field using a trigger?

hello @Neo, thanks for posting!!

to link a Field in a Table Record, you need to ensure the Table Record has been loaded and you can then use the Table Record > Link Record Trigger and define the Field youā€™d like to link:

once this Trigger executes, the Field will be linked with the ID of the Field you provide in the Trigger (which can also be a Variable):

could you let us know if this works for you??

thanks!!

Iā€™d like to ask how links are meant to be used. I found some articles like this that explain how to create links but not really how to use them in an App. You canā€™t query based on a field in a linked table, and you canā€™t Load a record based on anything other than its ID.

My current structure is something like this:

image

My goal is: given a combination of WorkOrderNumber, LineItemNo, OperationNo (which comes from barcode scans) to load all three of those records into placeholders. But, since for example LineItemNo is not an ID column (it canā€™t be, line items arenā€™t unique) I canā€™t figure out how I could Load that into a placeholder. I donā€™t think you can. In fact, I think the answer is that my Apps that are working on an ā€œoperationā€ (for example) MUST be working on a single table where the ID is the compound key I mentioned above (WO,L,O).

True or false?

Linked records are really powerful, I find myself using them all the time now. Here is a video of the basics: Quick tips - Linking records - YouTube

Iā€™m not 100% following your specific issue so Iā€™ll sort of talk around the issue in ways that Iā€™ve used linked records successfully in similar situations.

First of all, linked records are always seen as arrays, even if there is just one ID in the linked record field. This means that, for example, if you want to filter a query or an interactive table based on some linked value you need to use the ā€œis inā€ filter.

In your case, letā€™s say someone scans a work order. I would load the work order ID into a record placeholder and then use the linked line items field as an ā€œis inā€ filter for a query or table. Now youā€™ve got yourself the line items on display, however you like, as either a visible table or an array of unique items IDs associated with the work order.

This same technique can be used in either direction. For example, you might have an operation as your starting point. I would use the linked line items as an ā€œis inā€ filter to display the other items.

A couple other tidbits:

  • Letā€™s say you just want to get the single work order from the operation. No problem: load the operation, do get ā€œarrays>from index in array>index 0ā€ and grab that id, then load that one into items, then do the same to get your work order
  • Letā€™s say you want to display all of the information in a single table. This is a pretty new feature and I love it. For example, maybe you want to display the status of the work order right there while looking at the items table. But how?! The status is in the linked table (WorkOrders). Well, this relatively new feature lets you display fields from the linked table (so long as its a xyz-to-one relationship)

Does this answer your question? I realize it is a lot of text. If youā€™d like a video or if I misunderstood the question please let me know!

Thanks

1 Like

I actually prefer the written explanation, thanks for taking the time. If this works the way that I think I understand from what you wrote then this is actually amazing. Let me go try it, Iā€™ll post the results here.

OK. So I load Order 1234. That has six items, one of them is Item 2222. Now, I want to find that linked Item, but how do I use a ā€˜filterā€™ if there isnā€™t an interactive table?

Step 1 - operator scans the first bar code. That gets stored in text variable bcWorkOrder

Step 2. Guy scans the second bar code, which is item. That gets stored in bcLine:


.

The next action I want is "Using the currentWorkOrder placeholder go and search out the Item where Item.LineItemNo == bcLine ā€¦ then store that in a placeholder currentLineItem

The part Iā€™m missing is this:

image

I donā€™t know how to filter or use isin in a Trigger action.

@wz2b There are probably a lot of ways to do this. The one I would prefer to use is a query.
Create a query on the items table and an aggregation that is either the ā€œmodeā€ of the ID field or ā€œunique itemsā€. Iā€™m going to assume that your item that is being scanned is not the ID of the items table, otherwise this would be extremely straightforward.

Okay, so you have the query on the items table. Give it two filters, both app inputs. The first is an ā€œis inā€ filter on the ID field and the second is an ā€œequal toā€ filter on the line field (whichever has the scanned value).

Next, in your app, use the linked items records from the work order ID as your is in filter. It should cut down the items table to just the items within that work order. Then add the scanned line variable as the other filter. Youā€™ll be left with one record.

The mode aggregation will pull whatever ID shows up the most, as a text variable. You can use this to load the record. Alternatively, you can use the unique items aggregation to pull an array with one item in. Then you would do arrays>get from index and grab that value.

Make sense?

1 Like

Yeah. I need to create more sample data (and more barcodes) but I think itā€™s working. Itā€™s a little more complicated than I thought, I ended up using your Mode trick (thanks) because itā€™s one less trigger action than trying to use array.index(0).

If thereā€™s ever more than one row with the same wo:item thatā€™s a problem. The tables donā€™t enforce any kind of constraints, so I may write an offline referential integrity / constraint checker at some point. Weā€™ll see. Iā€™m curious what happens if I delete an Order record - does the delete cascade? or do the child records just become unlinked, and their link up is now empty/null?

Yeah I think you could run that risk - lines could be duplicated for a given WO. If it were me I would make sure Iā€™ve covered that scenario in whatever app or system creates the lines. Iā€™d also probably opt to concatenate WO-Line as the ID.

When you delete a record with links, the links are removed from the other records that reference them. Iā€™ve found this to be a pretty convenient feature - not having to chase down severed connections is nice. I also like that when you link a record that is already linked it doesnā€™t link it twice. Iā€™ve definitely become a linked record fan!

Hey @freedman I think I have this working, but trying to populate the data from node-red I ran into a problem:

image

I donā€™t think the Node Red tulip library has any support for handling links. Itā€™s just JSON though, is it possible to use

POST /tables/{tableId}/records

and include the ID of the foreign table (in my case a parent record) sent in as a string?

@wz2b you should be able to use the API to link records. You can find the details in the ApiDocs

Yup. But not the node red library.

OK. I 'll have to make a plain old HTTP request rather than use the packaged library.

I wrote Feature request: links Ā· Issue #6 Ā· tulip/node-red-tulip-api Ā· GitHub ā€¦ letā€™s see if anybody notices.

Lol, I think that repo is all one developer. Iā€™ll check with them in the morning

I have something else I want changed in that library and I said Iā€™d submit a P.R. Iā€™m willing to spend time improving it, but I know any P.R. would have to go through a Q.A. / release / regression testing / publishing process on your side, so before I spend a lot of time on it Iā€™d like a feel from the developer on whether or not theyā€™re willing to see my contributions through. If they donā€™t have time, thatā€™s fine, I may just fork it.

Weā€™re taking a critical look at library apps and envisioning better types of content to provide our users. Any feedback at all regarding the library content today is incredibly important to us. Please share!

Other than supporting links, thereā€™s two main things on my mind right now:

  • When you make a table request, the initial request needs to be passed to the output - at least the tableId, and I donā€™t see why not the entire thing. Currently my main use case for this is to use that tableId to look up information about the resultant columns. Thereā€™s ways to work around this in node red but itā€™s hard to avoid edge cases and concurrency problems. It would be a lot simpler if you just passed the initial request through as a different field of the msg
  • The other request I have is that we cook up some way to deal with finding tables and column information. Currently you have to know the table ID which contains a random component at the beginning. I have a pretty solid workaround for this today but itā€™s a piece of logic I copy and paste around, which suggests to me itā€™d be better to wrap it up in some utility methods in the node red library. My main motivation in saying this isnā€™t that I have a problem that needs to be resolved, but I have to imagine others using this library are going to have the exact same issue so a common solution is in order.
1 Like

This is a combination question/request on the topic of tables and links.

Question: if Iā€™m making a /tables request to create a new record in a table, can I pass an Array of IDs to foreign records in my link columns? In other words, can I do an INSERT that includes the links, rather than having to make a second request?

Feature request: if I canā€™t do that, would you consider adding the capability?

I have created a ā€˜linksā€™ node in the Tulip Node Red API and have submitted it as a pull request:

Hopefully this isnā€™t overwhelming @kcamenzind but I thought since this is on my roadmap that it might be better to get it in now. I donā€™t know about your QA / regression activities, but releasing a new package to npm and node-red is pretty quick (minutes). Itā€™s just a matter of what kind of reviews are required.

Iā€™m not 100% done with the links yet, but Iā€™m putting the P.R. in early so I can get feedback. Itā€™s still somewhat fluid until we get consensus.