Insert and Overwrite 2 tables from the same form

[PaulBappoo]

Hi All, my use case is a sales leads tracking system, where there are sales leads records and a sub table of records containing actions that take place for each lead.

I have a form that lets me create new lead lines

and I want to save the 2 parts of the form to different places.

The Date, Activity and Notes should be saved as a new record to the sales leads lines table (this bit is working fine) and the Next Action, Action and Notes should be saved to the sales header table (Sales Leads List), overwriting the existing 3 fields.

This bit is not working. From the screenshot above you can see that I have tried filtering in the overwrite to identify the record I want to update in the master table, then sent the values from my screen fields to the underlying table fields.

I can see that this filter is evaluating to 0, so that is probably the issue, but not sure what to do about it.

TIA

[AndrewB]

I can see that this filter is evaluating to 0...

Don't trust the message from the formula input area, there are a lot of instances once you get beyond basic building where it cannot tell if the formula is gong to work or not.

If you want to test it, add a Data Cell to the app temporarily, and paste the formula in there. If it still returns 0, then try this format: =FILTER(SalesLeadsList, “SalesLeadsList[id]=%”, $[ID])

The Date, Activity and Notes should be saved as a new record to the sales leads lines table...

In your screenshot, "and write to:" is referencing all the values in the "Next Action Note" column of the "SalesLeadsList" table. You're not targeting a specific row in that table, you're referencing all of them.

Instead, try this: =[Next Action Note]

Because you're inside an iteration (from the run option), the context row is now the current row that is being iterated upon, from your filter. So, since we already have a row, all you need is a column reference.

[PaulBappoo]

Hi Andrew, thanks so much for your response. I tried adding the $[ID] to a temp cell (excellent idea btw). You can see it lurking here under the Done button and happily showing it's value.

If I add the filter to the temp cell, even with the parametrised format, I still get a zero.

Which seems odd since the table has data that meets the filter

However, with the destination field set to SalesLeadsList[Next Action Note] it doesnt show a warning, removing the table reference produces a warning and neither of them result in the data being updated to the table.

As a slight aside, if I remove the filter altogether, then (using the fully qualified table / field reference) the data is updated to the FIRST row of the table. Which makes perfect sense of course - so seems I am stuck referencing an actual destination record.

Any further help will be greatly appreciated

[PaulBappoo]

Quick update, I tried something different: using the insert or update function, instead of overwrite.

Here's the setup:

And it performs the same, simply adds a new row to SalesLeadsList

We can see the MyID field value under the Cancel button evaluates to 4 and we can see the first record in the SalesLeadsList table HeaderID is 4, yet it still adds a new line.

Really struggling to understand if this is me doing something silly, or it there is an issue with the software.

[DanielTet]

Hi @PaulBappoo,

Here are is a way to fix the problem you are having, and a couple of suggestions for how to do this easier/differently.

First off I assume you want to Add a new record in SalesLeadsLines to the values of "Date" "Activity" and "Notes" (light green fields), and I see that you have this working. You are also trying to Overwrite the record in SalesLeadsList to the values of "Next Action", "Action" and "Notes" (dark green fields) - and this is the part that is not working.

(option 1) In order to make this work I have done the following:

1. NewID - is still a local Variable (not shared), but it is a Rowlink to the SalesLeadsList table; I've done this to make it easier to pick a record to edit.
   

   

2. I've put the fields that need over-written in a block of their own. This block's data field is =$[NewID] variable (form above)
   

   

3. I've (a) added all the three fields you have, as variables (not shared), and (b) I've also added the "Next Action (shared)" field, which is Shared to the row currently being viewed in this block.
   

   

4. In the automation on the "Done" button I have configured the "Overwrite" action as below. (Ignore the warning, there seems to be some aggressive warning around automations)
   

   

This works and updates the record in the SalesLeadsList table just like you wanted.

(option 2) However, as you can see from item 3(b) above and the "Next Action (shared)" field you do not even have to write an automation to save the field into SalesLeadsList table IF, the Block itself is set (data attribute =) to a row from that table (see #2 above). In other words if the screen you are working on is tied (ie. fields are shared from a row) then it automatically saves the data in that table when you edit it. All that you should have to do is add a new row to "SalesLeadsLines".

(option 3) And finally, you could also consider saving all actions/notes in the "SalesLeadsLines", perhaps with a "Completed" (equals to Y or N) field. This would be more normalized, in the sense that data of the same kind all lives in the same table (3rd Normal Form).

Let us know if works or if you have further questions.
Hope this helps,
DT.

[PaulBappoo]

Hi Daniel,

Thanks for the effort in looking into this for me, however I still can only get it to partially work.

I added the shared next action date field and used the filter and take date from =$[Next Action Date] and write to =THISROW()[Next Action Date] concept and yes, it was able to update the next action date on the sales leads lines table.

Also, I tied next action to the underlying sales leads lines next action field and this also sets the next action field on sales leads lines, without any automation.

However, if I try to update the next action note field using the same principle, i.e. take data from =$[Notes] and write to =THISROW()[Next Action Note], that field does not get updated in the sales leads list table.

The other thing I dont understand is is how the filter works, it seems to be saying find me a record on salesleadslist where the headerID = HeaderID - which is going to be all the records on that table, surely?

I would be expecting it to be saying something like fine me all records on the salesleadslist table where the HeaderID = the ID field of the record in the current form (which of course has been pre-populated with the record I want to update)

Certainly there are workarounds as you have mentioned. But with the option of tying the fields to the underlying table, when the new form is displayed, we are expecting the user to see the previously agreed next action and update and save it and then add the next agreed action. If we set the next agreed action field to the underlying table field it appears pre-populated with the value from that field, which the user then has to overwrite.

Keeping all the records in the salesleadslines table is an option that will probably work and I'll give that a try, however whilst workarounds exist, from an academic perspective, I am really trying hard to understand how Honeycode is supposed to work. In other words to be able to gain granular field level control over what the system is doing, since this is the only way I would really feel comfortable approaching clients with it as a potential solution.

Maybe this stuff is all down to it being in beta, which is fine and I would expect it to all get much clearer in the coming weeks. However, it does seem like, in an attempt to be able to call the product "No code", that a huge amount of clarity that one usually gets with code has been sadly lost.

If I was doing this with a combination of code and SQL I'd know exactly what values on what form I was picking up and what fields in what tables were populating those form fields and what fields in what table I was updating/overwriting or inserting. I expect this is just me not getting something important but elusive about the Honeycode paradigm, so I'm hoping I have a lightbulb moment and it suddenly falls into place. However, either way, I feel that the "what field am I addressing in what table and on what record" aspect needs a little attention from the developers to provide much more clarity in the environment.

[DanielTet]

Hi @PaulBappoo,

So sorry for not getting back to you sooner. Le me try to see if I can provide some help.

I've tried to save =$[Notes] to =THISROW()[Next Action Note] and it works fine. Perhaps the context was not set on one of the blocks or the Done button was not in the same block. See below for explanation of why it works for me, and of the Variable vs. Shared and Context setting. Hope that helps.

Longer explanation and background:

The lightbulb moment for me was when I understood Data Scope and/or Context - in other words where is the data stored and how is it available for use. The concepts manifest a bit differently than in a traditional programing language but they are there.

In Honeycode data is persisted in two places: Shared data is tied to a cell in a table (thus the [Next Action Date] in brackets), and Variable data is persisted in the browser or mobile app, more like a session variable (thus the $[Next Action Date] with the $ sign in front). If the data is Shared it will automatically save to the cell of the Row in the current context, if its Variable you have to use an automation to save it into a cell of some row.

Now where do you get the row in the first place, the context row, if you will. A "Blank Block" or a "Column List" block (they are both block as opposed to field/cell concepts) can: (a) have their context row(s) set to a row/record via the Data property. (b) have no context row or record set. Here is an example of (b) first:

Notice how the Date, Activity and Notes have to be variables because the Blocks's Data is not set to anything. Notice also how the New Line ID, the 1, is actually tied to some data, because it is
of type Rowlink, from a table. In fact this is where we let the user set a variable $[NewID] as follows:

This is important because it shows that a single cell can hold (the reference to) an entire row. In fact I set the value of an entire Block to this variable. So when the user picks a different value in the pick-list the block changes (Specifically the Next Action (shared) changes, the green variables with $ in front of them do not change):

So in short, if you are referring to:

• $[VariableName] you are referring to a session/screen varialbe
• [VariableName] something that is in a table

So this filter works (in the automation on the Done button) only because:
=FILTER(SalesLeadsList,"SalesLeadsList[HeaderID]=%",[HeaderID])

• the button is inside the block
• the block is set to a row (as shown above)
• in a block context you can simply refer to the field by it's name, in this case [HeaderID]

I probably loose some points for style by using =THISROW()[Next Action Date], I should have been more consistent and written it to above and used =[Next Action Date] like so:

Technically =THISROW()[Next Action Date] also works because the Block the button is in already has been set to the row selected by the picker. So technically it works but it can be confusing.

Hope this long explanation helped show the difference between Shared and Variables, and how the block context helps simplify and refer to fields from the table.

Please let me know if you have more questions.
DT

[PaulBappoo]

Hi Daniel, many thanks for the further explanation, I will have a play around with all that and see if I can make it work. Certainly that all seems to make sense and is probably the best explanation of the block context, variable and shared fields that I have seen to date, so I am hopeful I will get it once I have some time to try. Thanks again.