SELECT (multiple) CASE query doesn't work
Hi, I'm really satisfied with Zoho services, finally i've found what i need!, but now, i have got the current problem. Just imagine Formula 1 scores. When a driver ends in 1st position then scores 10 points. 2nd = 8, 3th = 6... etc My query only returns the first case, and ignore the other ones. In my table, column 'Pos' is a Lookup Column. SELECT Driver, CASE Pos WHEN Pos = 1 THEN 10 WHEN Pos = 2 THEN 8 WHEN Pos = 3 THEN 6 WHEN Pos = 4 THEN 5 WHEN Pos = 5 THEN 4 WHEN Pos = 6 THEN 3 WHEN Pos = 7
DQL Query - Date format different in preview and view mode
Hello, I have the following SQL query: SELECT Max("Date"), "Person", "Name", SUM("Pcs") '# of Pcs' , SUM("Hours") 'Hours', ((SUM("Pcs")/SUM("Hours"))/60) 'Pcs/min', Sum ("Scrap") Scrap FROM "BCoil Report" where DATEDIFF(CURRENT_DATE(), "Date")<=60 group by "Person", "BCoil Name" In preview mode, I am able to see the date in the format "yyyy-mm-dd 00:00:00". However in "view mode" it converts it to what I think is text. E.x "[B@6803f59b" How do I fix this? As well, any suggestions on how would I change
Extracting a "Week of" date from another date
Hello, I would like to use pivot tables to view sales activity by week. Currently we enter the exact date of our activity. What SQL function(s) can we use to extract the date of the previous Monday from a date field? Any suggestions would be appreciated. Thanks Jim
Problem Exporting from ZoHo
I successfully created a small membership database, but cannot export the data. When I try to export the database or a report as a CSV, a File Download window opens. I have tried clicking OPEN and SAVE buttons and neither will result in the data being exported. The File Download window says - Getting File Information - but it just runs and runs. No file is ever exported. Please advise as to how I can export the full database and reports without the process "hanging". Thank You. ~Barbara MS Vista
Does DB support Aliases?
I keep getting a "Specified Query is not yet supported by Zoho DB. Please send us your query to support@zohodb.com, in case you wish to post to our bugs database" message when trying to alias a SQL field. Is this only me? Or do any other users run into the same issues? What's funny about this is that when I click on execute the first 10 rows show up exactly they way I want them too, but when I try to save the query, the "not supported message pops up. Thanks, Jim
Date Field Bug - Formatted Field & Now Won't Accept Data
I have a date field in my database that I have recently formatted to MM/DD/YYYY format. It appears that the field is now locked and I cannot enter new data into it. I have tried using the built-in calendar function, typing the date in and copying and pasting the date into the field to no avail. I am getting the following error: Date value should follow the pattern '05 Feb 2008 17:30:30'. The given input is '24 Jul, 2008 10:56:43' I have also tried resetting the column format to what it originally
HELP: dB for tracking volunteer hours
Hi, I need help in creating a db for school PTA. This is to track down volunteer hours and that each parent could view only their own data (password protected). Any suggestion or if someone could help me with the template. i appreciate any help thank you, jc
Add column data to existing table?
Anyone know how to add column data en mass to an existing table? I have a table with members in it and I want to add a column with ID numbers in it to the table. I have the ID numbers and the table with the members in it sorted. I need to figure out how to add those numbers to the table so I do not have to type them all by hand. Cheers, Dave
Pivot table options
I have a very simple DB - in columns - Date, Agent ID, Client, Number of items sold. When creating a Pivot View I am putting the date in the Column, Agent ID in Rows and then number of items sold in data. I do not have an option to select sum in place of count. How can this be fixed?
Export Options
Please clarify the options for exporting data and metadata from Zoho DB. Can you, for example, export DDL to a file (e.g. XML)?
Averaging Function
Hello, I hope someone can help me with generating a production report average. I have defined a table with the fields "Date", "Name of operator", "Pieces produced" and "Hours worked in shift". Id like to know the average number of pieces produced per hour by each operator over the last 30 days. I created a Summary view telling me the total pieces produced and total hours worked for each operator with a date filter for the last 30days. Id like the summary view to have an additional field where I can
Charts not updating
Hi there, Not sure if anyone can help with this. I have a table and a chart that produces a line graph based on this table - filtered. When should the chart update? Straight away? Is there a way to force the chart to update. I have added 2 new rows to the table and have now left it over night and still the chart has not updated. Any help would be good. Thanks Tim
SQL Functions
I want fo format a numeric field as a string with leading zeroes eg Format("PositiveNumberField", '000') in a query table, but the SQL gives an error on the above. How can I do this ? The reason i am having to do this is that I wish to display a number field in a summary report and it insisted on it being a group by field and not a value field. Am I taking the right approach or is there an alternative ? Thanks
File attachments field type
Zoho Creator allows for file attachments. Will this field type eventually be supported by Zoho DB as well? Especially with the prospect of creating forms through the coming API this would be highly welcomed.
DBF file compatible
Can you include dbf file filter to writer, DB and wiki? Can you make ZWiki compatible wiht TWiki files? Import TWiki files to ZWiki and writer and DB?
Printable reports?
Hi, Is there a way to create, format and print report from a table, say for mailing addresses? Seems like the only view I can get is the spreadsheet format. What I'm looking for is something similar to Mail Merge to create labels from a table. The best way I've figured this out is to 1. Create a report and save it 2. Export it 3. Use MS Word Mail Merge to create labels 4. Print it Is there a similar capability in Zoho DB? Or would Zoho Creator be a better option? Thanks.
if then formula for text input
I have a members table in DB. One field in that table is Date of Birth. I would like to have another field that shows a level of membership available based on a calculation using the date of birth and today's date. For example: A person born before 31/12/2991 should return a value of "Cadet/te". If someone is born later it would return "Junior". How would I do that? Dave
Query Based on Date
I have a table where I track club affiliations. Each affiliation has a start date and end date. I would like to make a query that displays only affiliations that start after a certain date but I cannot get the SQL to work. Here is my statement. SELECT "ClubName","Region","ClubWebsite","VenueName","Address1","Address2","Town-City","PostCode","VenuePhone","startDate","endDate" FROM "Clubs","Club Affiliations" WHERE "startDate" >='2007-09-01 00:00:00' I get no records even though I have several affiliations
SQL Syntax problems
Hi, I'm trying to execute a join and am having trouble. Could you please have a look at the code and tell me where the problem is. SELECT * from "FT-EMBA-Rankings" join "names" where 'FT-EMBA-Rankings.ID' = 'Names.ID'; Thanks. Richard
strings and numeric calculations in same field
I have a fairly complicated query, which includes a field that has strings and numeric calculations concatenated. It looks something like this: SELECT INT(COL_A) || ' to ' INT(COL_B) FROM ... When I execute it in the edit mode's preview, it looks okay. when I save the query and execute it in view mode, it puts gobbletygook in there (looks like it might be a pointer). EG: [B@619a1a10c they all begin [B@ followed by a number in Hex, which makes it look even more like a pointer. - Paul
Setting missing values to null for sorting
Hi, Could you please tell me how I can sort columns in ascending order for columns that have missing values. At the moment if I sort like this, all the rows with missing values appear first. Thanks Richard
How do I limit the number of columns displayed in embedded
Hi, I would like to display only certain columns of my DB on my various web pages. Can you please tell me how to do that, and also how to disable the editing function in the website embedded table. I do not want my site visitors to change my data. Thank you Richard
Multiple users on the same table
Hi, I noticed that two different users can log into Zoho DB using the same username and password (which is fine) and are able to open the same table simultaneously as well as make edits at their respective ends in the same table, and there is no warning given to either one that the table they're editing is being edited by another user. Is this a design feature? And, how can I get around this so that only one user can edit a table at one time? Shouldn't a table be locked down when it's being edited
How to sort on multiple columns in a table?
As the subject line says, I would like to sort data in a single table based on multiple columns in order, for example, first by "State", then by "City" then "Lastname." Is there a way to do this using the "Sort" button in the table view, or will I have to write an SQL statement under New Query Table? Thanks.
Leading zero for Zip code disappears
Hi, I exported data from a table view and opened it using MS Excel. The table has a column for Zip code that has values with leading zeroes. The exported file deleted the leading zeroes for the zip codes. How do I ensure that the leading zeroes are not deleted when I export data? Thanks.
Column Widths in Reports
When I view my report the column widths are set too small cutting off names. How do I set the widths of the columns in reports. http://db.zoho.com:80/ZDBDataSheetView.cc?OBJID=39855000000002395&STANDALONE=true I would also like to use the ID column to dictate the order in which the species column is presented, but don't really need or want to see the ID column. Is it possible to "hide" a column, or is there another work around. here is the end use of the report - http://bostonbirds.org/bimbovpi.html
Hiding Sheets
Is there a way to hide certain sheets from those you share your database with? I have a bunch of sheets with lists on them and I don't want people to see these.
Date Format - Month only
Hi, I'd like to have users enter in only the MONTH into the database. Basically a MONTH() in PHP. thanks! Your products are truly amazing - keep pushing the web.
Sort an aggregated set
Greetings, I have need to aggregate the results of an expression, then sort by the aggregated column. I've tried to do this a number of different ways, but I get an error each time. It appears that we are unable to sort a 'Summary View'. And it appears that I am unable to place the 'group by' on the query table that I need. Here's my query.,.. SELECT upper("Site"), sum(ROUND(("Per Month Income") * ((YEAR("Next Paid Date") - YEAR("Date Paid")) * 12 + (MONTH("Next Paid Date") - MONTH("Date Paid")))))
number of decimal places - 2 vs 2.0
When I choose the "sum" option for a Summary View the quantity is listed as: 1.0 or 4.0 when all I really want is a whole number (1 or 4). Is there a place to set the number of decimal places? Also in Pivot view the qty line reads "Sum of ____" - can that be editted to read "Total" instead? Thank you!
Multi Line Text
When entering text into a "Multi Line Text" cell how can I force or determine where the text or copy gets broken. If I use the "enter" key I leave that cell. It is probably very simple but I haven't been able to figure it out. Example: One Two Three Thank you.
DB documentation
Is there any documentation available for Zoho DB & Reports?
Zoho DB (Query) not correctly querying
To test the DB & query I've created 2 tables: AGENDA (Nombre Text, F_Nacim Date, Cod_Postal Number, eMail email) Data: Fer, 11/25/1969, 28823, ppp@gmail.com Ceci, 12/11/1967, 28823, (nothing) Sofi, 07/04/2000, 28823, (nothing) LOCALIDADES (Cod_Postal Number, Localidad Text) Data 28823, Localidad_Description Then I've done this select-SQL: SELECT a.Nombre, a.F_Nacim, a.Cod_Postal, a.email, b.Localidad FROM Agenda a left join Localidades b on b.cod_postal = a.cod_postal order by a.nombre The query
Graph only shows one series
I have built a query table that contains data regarding golf scores. It has the following columns: player, course, total (this is a sum of other columns in the main table), date. I want to graph the following: X axis is date Y axis is total the data points are filtered by a couple of players and a course. I can't get the graph to split out the players individually. it works fine for one player/one course but i want to compare players on the same graph. How can i get the graph to do this? Thanks for
How to Filter reports and sum data columns
Hi there, I am having some problems and I may be missing something with how i can trying to do it! Firstly I can filter my table I can not seem to filter a Report based on a table. Is this possible? Secondly I want to be able to chart but based on a sum of some columns. e.g. table contains date, name and data columns. I want the X axis to be date, Y axis to be the sum of the data columns and therefore the labels to be the names. Any help would be appreciated. Thanks Tim
JDBC support or API for any kind of popular language?
Hi, Is there any plan for JDBC support or API for any kind of popular languages out there, like C, C++, Perl, Python, etc. Or zoho DB is going to be strictly on-line access only? Thanks,
How do you insert a column in a database?
I want to add a column between two fields and the only option I appear to have is to add it to the end of the columns already defined. I don't see an "insert" option, only a "delete" one. Am I missing something? HOw do I insert in a specific place in a table? thanks, Santwana
Forms for data entry
I can't seem to find any way to make forms. Am I missing something or is this function not available? Hope
query tabel
i created a table by importing data from excel. i need to query tabel, but i cant see any options in the window for tht. how could i do that? i took zoho DB. i would also like to get charts about the table.
Possable to use Zoho DB with Flash Mx [Widgets]
I'm learning how to use Flash MX - [Widgets]. Is there a way to connect to my Zoho DB and send Querys? Edit: Sorry if I posted this in the wrong section.
Next Page