I suspect this is an easy one for the more experienced folk here… but I’ve scratched my head on it a fair bit today without coming up with an answer.
I’ve got a button in a table essentially just adding a new row to another table with small bits of data. And I noticed that in this table, one of the columns had two different types of - er - format (?) for the same data when displaying it, even though in the original table, all the data looks the same.
A picture tells 1000 words.
Here’s the data in the new table put there by the “add row” button action.
In it, columns 1 & 2 are added by the button. Column 3 is just a formula looking up the parent table using the first column ID
Now - notice the light blue job number/names. And some are even from the same project (so the data is coming from the same master project table at some point!).
I made the third column in the hope of figuring out what was going on - and noticed the square brackets in the data. What does this mean? I’m hoping it might help me track down why theres two different forms for the same data.
I suspect its a case of some data being placed by a button, but also being editable. Or some such thing. Its a fairly complex set of tables (for me at least) and tracing how its happened is tricky. I can’t replicate it with dummy data - or at least, haven’t managed to yet!
So here’s the table the data is pulled from (coming from bookings)
Which is coming from projects (created with a button!)
Looking through everything now, I think there is a better way of doing things, which is just using job numbers as the main identifying piece of data, and using referenced columns if I need to get the job name etc.
This is what happen when documents are made over time - and when you’re USING the docs as you’re making them. Eek
I do it this way (rather than related columns in the invoice table) so that one can edit the info (as sometimes small changes are needed in the info before we send the rows from the same project over to XERO to actually make the invoice. (Still working on this implementation, but tests have things working nice and smoothly through zapier)
I suspect something has happened when some data got deleted, and I’ve replaced it in the wrong way… perhaps?
And here you can see that the job numbers that appear differently in my first screenshot in the first post also are different here. They DON’T have the square brackets.
So my first question - is where do the square brackets come from?
This data is placed into the booking from a button in my project. But I think what happened is someone accidentally deleted a few bits of data and I just put it back in by typing in the project ID (rather than being placed in by an action looking up the data!)
So - the action in the project looks like this :
And the booking is getting the job number from the task.
I realise this is SO hard to explain in a message like this. But I also feel like I’m getting much closer to figuring it out…
Thanks - I’m creating a dummy chain of tables to see if I can replicate / understand whats going on.
I suspect you’re pointing me in the right direction.
Would love to get a pointer as to what the “[data]” vs “data” in a column actually means…and if I could maybe use a formula somewhere to clean it up…!!!
The brackets usually says it’s a list (can be a list of one item)
Do you use Filter() function with this data somewhere? If so, try to append .first() to the filter
The filter returns a list .first() turns this list into a single item.
oh I’m almost SURE this is where the problem lies. I have been using filter quite a bit. I feel like this little piece of info (that the [ ] means list) is exactly what I need to know.
I’m really struggling with being sure that I’ve setup my whole doc in the correct way… I fear I’ve made quite a bad mistake somewhere (even though it mostly works. Ha!)
I’m getting closer but think I’ve worked out something fundamental that I don’t know. I just re-read everything I could on filter and lookup, but it didn’t help.
See this test. Two different ways of entering the job number into test 2 table - one just using text and one using lookup (which I love in order to get quick info of the parent table). Both can be entered from the button above or manually.
YET - look at the two final columns in test 2. One uses the filter command as I understand it - and it works great. But using it based on the column Job Number V2 (which is a lookup) means it doesn’t work. And while I have managed to get it working elsewhere in my doc, I think its more by brute force/chance rather than understanding what is actually going on.
And I really need to understand it hey - otherwise I waste a lot of time bothering folk here / trying to debug my tables!
So this works:
Ok - so I’ve figured something out.
I was looking for a way to insert the ROW into test 2 via the button, and its as simple as just doing =thisrow() in the action.
Then looking closer at what data that actually leaves, it is @K0003 - and the @ of course is VERY important.
So - given that column is editable, when some job numbers were accidentally removed, I didn’t manually add the @ before the job number.
And while it still worked (because of how I looked up the other columns), it causes me the issues I’m having / outlined in the intial post!
AH - its so good to figure that out.
Oh - and you’ve just posted the other little bit of info that I needed to understand. YES! I think I’m getting there.
Just scanned through the thread, and it definitely looks like a one vs list-of-one issue (and maybe even a list-of-list-of-one further down), which started somewhere deep down and propagated like a virus throughout all of your data. That’s why it is important to always check for proper data types and notice and fix issues asap.
Related thread:
Examine all your Filter() formulas closely. Properly set “allows multiple values” on columns themselves. If you expect one value, use .First() after .Filter(). Live formulas should fix themselves after that. And for copied data make buttons that would automate unwrapping those potential lists, e.g.
P.P.S. Also get into habit of programming button actions with formulas and not by selecting dropdowns. In my experience it’s much easier to spot errors in formula code.
Could you show an example of what you mean by this? I’m trying to work through it but don’t quite get it…
Copied date - check. Guess you mean copying through a button?
automate unwrapping those potential lists
Not sure what this means… unwrapping seems to me you could mean converting something from a list to text?
Or unwrapping from a row to text?
Looking at the formula you wrote out - doesn’t that re-wrap the data into being a list?
thanks all for the help (almost) understanding
(I have bookmarked all this so I can go back to it as I try and trace back to the root of the problem.)
Oh wow. Reading through your linked thread now.
I NEED to understand this stuff. Its completely related to what has gone on here!
Maybe a morning coffee first will help kick my brain into gear.
I mean is that if you’ve got lots of rows and it’s tedious to go through all of them and manually change all the values to “ITEM” (as opposed to a LIST OF ITEM or even more nested), you can create a button column in your table that will do the replacement for you automatically. Then just make another button to press all those buttons in one go.
By unwrapping I mean converting
a list of one
a list of a list of one
a list of … of a list of one, potentially
to simply an item.
ListCombine() takes whatever the nested list you have and converts it into a flat list of one item.
Then First() selects the item from that list. This is what I mean by unwrapping
Oh YES! Thank you. This makes complete sense now.
And I guess part of the trick is being very careful when having a list is useful, and being careful to treat the list properly. I feel like I need to stick up a little poster beside my workstation which reminds me of things like this…
Do you REALLY need this to be a list?
Do you REALLY need this to be a multiselect?
etc etc
I’m a composer - this stuff doesn’t come naturally.
I’m going to slowly go through each of my tables and see if I can identify the problems / re look at all my formulas (or re-create formulas for buttons rather than using dropdowns.). Time I don’t really want to use now but I’m pretty sure its going to save me in the long run. Right???!
Just checking… but feel that I am right in using a formula like this to find all the contacts that belong to a company…
[Klang Contacts].Filter(Company.Contains(thisRow))
Needs to have the possibility of being multiple…or one… or none.
