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??
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:
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!
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:
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.
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:
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
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.
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.