Let’s say I have a table, that is connected to a dataset with two fields: City & State. How can I join the two fields together together into one column of the table simply as “City, State”.
let cols = $w("#table1").columns;
cols.push( {
id: "col1",
dataPath: "city",
label: "city",
width: 100,
visible: true,
type: "string"
} );
$w("#table1").columns = cols;
Hey David,
You could use rows property of table to set the data as you desire:
let rows = $w("#table1").rows;
let city = rows[0]["city"];
let state = rows[0]["state"];
let cityAndState = `${city}, ${state}`;
rows[0]["city"] = cityAndState;
$w("#table1").rows = rows;
For more info, please see the documentation.
Ohad – Much appreciated, however basic quick question. This only works for the first row [0]. How do I apply the rule to all rows?
Answering my own question here, I built a loop, which as a non-coder I am proud of. If there is a more efficient way to do this, I would be curious. Mainly because I am just trying to learn.
$w.onReady(function () {
$w("#dataset2").onReady(()=>{
wixData.query("Project_Submission")
.count()
.then( (num) => {
let count = num;
console.log(count);
for (let i=0; i < count; i++) {
let rows = $w("#table1").rows;
let city = rows[i]["city"];
let state = rows[i]["state"];
let cityAndState = `${city}, ${state}`;
console.log(cityAndState);
rows[i]["fullAddress"] = cityAndState;
$w("#table1").rows = rows;
}
});
});
});
Hey David,
Loop looks good 
A small suggestion: you don’t need the whole dataset thing to get the count of rows.
just use:
$w('#table1').rows.length
As your loop ending condition.
BTW, You can also use
rows.forEach(...)
Whichever way you choose, I have two suggestions for you.
First, move the following line to be before the loop block of code:
let rows = $w("#table1").rows;
and the following line to be after the loop block of code:
$w("#table1").rows = rows;
This way, you get the whole data from the table only once, and you set it back only once, after you’ve done updating all of the rows (instead of doing this in every iteration of the loop).
Liran.
Liran – Incredibly helpful. It immediately sped up the load time. Thank you so much! Unfortunately, now I have a new issue. I also have a keyword search on this table. Now, when I perform the keyword search, it resets the table values, but doesn’t maintain the revised table values we created.
What is the best way to have the new row values stay even after I change the table rows? I tried adding the bolded text below. Essentially I run the function OnReady, then try to run it again after the keyword search. It doesn’t work.
Thoughts?
$w.onReady(function () {
setview($w("#table1"));
});
export function textInput1_keyPress(event) {
wixData.query('Project_Submission')
.contains('orgName',$w('#textInput1').value)
.find()
.then(res => {
$w('#table1').rows = res.items;
setview();
});
}
function setview (x) {
$w.onReady(function () {
$w("#dataset2").onReady(()=>{
let rows = x.rows;
let count = x.rows.length;
// Full Address
for (let i=0; i < count; i++) {
let location = rows[i]["location"];
let streetAddress = rows[i]["oppLocation"];
let city = rows[i]["city"];
let state = rows[i]["state"];
let fullAddress = `${streetAddress}, ${city}, ${state}`;
if (location === "At Location") {
rows[i]["fullAddress"] = fullAddress;
} else {
rows[i]["fullAddress"] = "Remote";
}
x.rows = rows;
}
});
});
}
Hi David,
Regarding the onReady functions:
As you can see, onReady is a function that receives another function as a parameter (which is called a callback function ). This function will run somewhere on the future, and only once, when $w(‘whatever’) is available for use.
Lets take your code as an example and have a look at a timeline when navigating to the page:
page is loaded → $w is loaded and available* → ‘dataset2’ is loaded**
Therefore, whatever function you supplied to $w.onReady (same goes for $w(‘#dataset2’)) will only run once, and only when the element is loaded.
Now, back to your issue:
If I understand correctly, you only need to filter whatever it is you show the user.
This means that you can fetch data from the database only once, then keep it somewhere and manipulate it.
So my suggestion to you is to go back to your previous implementation, and try something like this:
let originalRow = [];
$w.onReady(function () {
//some code here...
// your loop here
originalRows = $w("#table1").rows;
}
So basically you save the original data of the table (including the full address) in an array.
Now, inside textInput1_keyPress you only need to use filter method on this array, for example:
const valueToFilter = $w('#textInput1').value;
$w("#table1").rows = originalRows.filter(item =>
item.orgName.includes(valueToFilter)
)
This will also work faster because you won’t have to read from the database on each keyboard click.
Hope this helps
Liran.
Liran – First, huge thanks for your responses. As a novice Wix Coder, I sometimes feel like I am slamming my forehead against my keyboard in an attempt to learn. Rewarding, but painful. With that said, I am taking in every resource you send my way, so please don’t think it is falling on deaf ears. Still, I just cannot get this to work.
Two main issues:
Sometimes when I preview the page, the table loads correctly as intended with the loop. Then, if I exit preview and reload, poof, it changes and it’s all gone. Despite, no changes to code. I have to exit the page then comeback for it to work. Super confusing.
The filter you had proposed does not work. Sometimes the console will read Script Error once I start typing into the Keyword search. Other times, it will not error, but it just filters the table to blank results.
I am lost at this point.
let originalRows = [];
$w.onReady(function () {
$w("#dataset2").onReady(()=>{
let rows = $w("#table1").rows;
let count = $w('#table1').rows.length;
// Loop
for (let i=0; i < count; i++) {
let location = rows[i]["location"];
let streetAddress = rows[i]["oppLocation"];
let city = rows[i]["city"];
let state = rows[i]["state"];
let fullAddress = `${streetAddress}, ${city}, ${state}`;
let skills = rows[i]["skillsRequested"];
let specializedSkills = rows[i]["specializedSkills"];
let hoursEstimate = rows[i]["hoursEstimate"];
let mergedHours = `${hoursEstimate}`;
rows[i]["placeholderHours"] = mergedHours +
" Hrs";
let price = rows[i]["price"];
let wage = `${price}`;
rows[i]["placeholderPrice"] = "$" + wage;
if (location === "At Location") {
rows[i]["fullAddress"] = fullAddress;
} else {
rows[i]["fullAddress"] = "Remote";
}
if (skills === "Specialized") {
let specSkills = `${specializedSkills}`;
console.log(skills);
rows[i]["skillsPlaceholder"] = specSkills;
} else {
rows[i]["skillsPlaceholder"] = skills;
}
$w("#table1").rows = rows;
originalRows = $w("#table1").rows;
}
});
});
export function textInput1_keyPress(event) {
const valueToFilter = $w('#textInput1').value;
$w("#table1").rows = originalRows.filter(item =>
item.orgName.includes(valueToFilter)
);
}
Hey David,
Please do not feel lost, it’s natural to be a bit confused when it gets to code.
Can you please share a link to your site (or editor)?
Thanks for the support Liran! I was able to get it to work. For some reason the event handler was causing the error, despite it being correct, at least from what I was able to tell. I just deleted and re-created it. Works fine now.
Next and I think final issue for this is the includes() is case sensitive. Is there an easy fix for this? Code is the exact same as posted above. So, a search for “wix” would not return “Wix” if that was one of my organizations.
If you don’t care about case sensitivity, use only lowercase/uppercase comparison in your code:
$w("#table1").rows = originalRows.filter(item =>
item.orgName.toLowerCase().includes(valueToFilter.toLowerCase())
);
Should do the trick
You’re a hero. Thanks Liran!
Liran – Or anyone who can help for that matter I have another question
Basically, I want to filter my table according to the values returned by my query. In theory a HasSome works well here, however, I don’t believe the code works when filtering tables, just datasets.
Right now, the code below queries a database of Applications, according to which applications were made by the current user, returning the corresponding projects. Then, I want the table to filter to the corresponding projects. If the number of projects returned was a single value, the .includes function works great, but a user can apply to multiple projects, so that doesn’t work.
Thoughts??
wixData.query("Project_Applications")
.eq("_owner", userId)
.find()
.then( (results) => {
let items = results.items;
let projSubmissions = items.map( items => items.proj_sub_id);
console.log(projSubmissions);
$w("#table1").rows = originalRows1.filter(item =>
item._id.includes(projSubmissions)
)
Hi again David,
According to this code, I can see that you are getting all of the projects submitted by a specific user. This means that inside on ‘.then’ you are already half way filtered.
Now, all you need to do is filter out the ‘proj_sub_id’ that are matter.
I’ll take a wild guess and assume that you want to filter it using a text input, lets say it’s ID is ‘textInput1’.
This code is using Array.filter() and should do the trick:
wixData.query("Project_Applications").eq("_owner", userId)
.find()
.then( (results) => {
const items = results.items; //already filtered by userID
const textToSearch = $w('textInput1');
//following line will filter out the project name according to the search text
const projSubmissions = items.filter( item => item.proj_sub_id.icludes(textToSearch));
$w('table1').rows = projSubmissions;
);
Liran – The Project Applications dataset and Project Submissions dataset are separate, so I don’t think your solution is entirely correct. Thoughts?
Essentially, I table1 shows all active Projects. However, I need it to only show projects which a user has applied to. So, I query the Applications dataset for anything applications that were submitted by the user and retrieve the respective applications Project Submission ID. Then I want to filter table1 according to that Project Submission ID.
Oh… I think I got a bit confused with the data model.
Can you describe the data bases you have and the fields inside of them?
Also, can you please share a link to your site?
Liran – I actually found an alternative solution, but as usual I am shooting form the hip, since my only code experience with Wix Code. So, feel free to advise if there is a more efficient way. Also, I know it may hinder the help, but I cannot publicly share website at this point in time.
Databases: For all intents and purposes, I have three different databases:
1) “Member_Database” : Stores all relevant member information
2) “Project_Submission” : Stores information regarding projects or jobs. These are submitted/created by organizations. Think of these as volunteer projects that Members can apply for.
3) "Project_Applications ": This stores all applications, specifically when Members sign up for a specific Project Submission. This database contains Member._id and Project_Submission._id, so it really is the link between the first two datasets. The intent was to structure these as Entity Relationship databases, so I can query joins.
So, on each Member’s profile page, I wanted to create a table or tracker of sorts, which shows which Projects they have applied for. I came up with an alternative solution below. Essentially, I restructured the code so I query the Applications_Database for any applications the current user has made, then I find the respective Project_Submission for each application, then place those rows into my table. It works well, but am not sure if it is the most efficient if I start adding thousands of lines of entires to the database
wixData.query("Project_Applications")
.eq("_owner", userId)
.eq("status", "Accepted")
.find()
.then( (results) => {
let items = results.items;
let projSubmissions = items.map( items => items.proj_sub_id);
let projApplications = items.map(items => items._id);
console.log(projSubmissions);
wixData.query("Project_Submission")
.hasSome("_id", projSubmissions)
.find()
.then( (results) => {
let items2 = results.items;
$w("#table1").rows = items2;
Hi all. Please help
How can I join two fields together?
I have 2 fields
First Name and
Last Name. The users enter both into 2 separate fields.
I need to display reports or other labels with their First Name & LastName in one field called Name. HOW?