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