How do i get my numbers $ to format right?
I can not get my imported in number - totals to format to show right. I have changed the formatting to every setting in their and none work to fix it.
This is how they come in from my CSV files.
But this is how they show up in ninox. I can not send someone a payment recept with the numbers looking like this hunderds are turning into thousands.
19 replies
-
To confirm, all your fields in the picture are text fields?
Since your data from your CSV has $ in it, Ninox will treat any data with non numbers as text. So the first thing to do is to strip the $ and convert the text to number.
If all your data is consistent then in a new formula field you can put this in:
number(substr('Total:', 1))The substr command takes the field Total: and says give me all text from character 1 to the end. Remembering that Ninox starts at 0. Then the number command transforms the text, which is now just numbers, into numbers. Now you can format it in the field using the Number format section of the field.
I hope this helps.
-
Hi thanks but i can not seem to get this to work it will not let me put it in a formula see screen shot and a screen shot of all of the fields. and a full screen shot of what i have going.
This is all test data i have in to get it working beofr addin in teh real customers.
what is strange is when i make a test invoice and add in $400 it formats that right.
All the total fields i set as number fields should they be text?..
-
-
Total: is a number field?
Are you formatting Total: in Ninox? Are you adding the $ in Ninox or did it import that way?
You said you CSV has $ in front of the number, so I shouldn't have imported into a number field.
Sorry, my mistake it should be the full word substring not substr, but this only works for text.
-
I am not adding the $ it is in the CSV allready. Here is a screen shot of part of a CSV.
So what woud i do to fix this?
-
Something is not making sense. Can you upload this DB?
-
Yes here it is. I did get some to load in right but some do not.
Now i have bigger problem i made another version of this all cleared it out and loaded in my real customers and for many of them the dates did not show up but are their all in the same CSV file in the same format.But even worse none of the dates are right for each customer.
It is showing dates we have not booked and every date is wrong like it just picked dates at random.
Here is a screen shot of this.
This makes it not usible like this..
-
Here is the DB
-
Looking at the fields, you are formatting the number fields in Ninox. If you edit Total2: you will see that there is formatting applied under Number format. If you click on Number format and click on empty then there is no more formatting and the $ goes away.
Now back to your original question, I have a sample Bamboo Sunshine csv and when I import the data everything imports fine. I'm surprised that Ninox can handle the $ in the CSV and import just the number into the corresponding number field.
I tried changing the number in my CSV to add ".00" and when I did it Ninox removed the trailing zeros. Then I changed it to ".43" and Ninox imported the number correctly.
So if you turned of all formatting for the field Total: and then look at this particular record that is having the incorrect data what is the number that is shown.
-
About your date issue.
Looking at your sample CSV you sent in another topic, the date format of 4/12/22 is not liked by Ninox. Playing around it seems like Ninox is ok with yy-mm-dd with month being in three letters. I tried months with numbers and it didn't like it. So if I did 22-Apr-12, Ninox recognizes it as a date and will import it.
You can either change the date in your CSV file using a spreadsheet program that will change the format for you and then save a new CSV. or
You can save the date info as text in Ninox then run a script that will set your date fields properly.
-
Ok thanks that worked i changed the date format and that fixed the problems and the numbers are working right now to.
Fred thanks for helping i would have not got all that. I was about to give up on. the template and just load into a blank DB.Here is a new updated version of the sample db. I added in some full jobs just changed the customers info. And i removed all the stuff that was not workign right.
2022 Biz Database new Bata.ninox
I would like to remove some things but not sure if this will break it or not. Also re-name some things for the invoiceing. Not sure if re-naiming will break it.
I do not need or use Vat rate or amount Vat when making invoice can they be taken out with out breaking it?
Here is the other view that has Vat rate i could not find how that is coming in to take it out.
I also get this side ways M what is that can i take that away?
Then when in edit mode i see two what looks like problem tages. Is this saying i have a problem someplace in the two areas or is this normal to see in edit mode?
Is it safe to change everything that says invoice to - Payment - we will only be taking in payments. Or will that break it as it is looking for the name invoice.
I now have all my customers loaded in the invoice template so i can now at least find a customers info right away at last.
Next i will need to see about usign the invoice more for payments.
So when a customer makes a payment i can enter it in as a invoice/Payment and it updates the Total Still Due to show the new amount after this payment tracking it.
And sends to the invoice for the customer print out showing the full price of the package they originaly ordered. Amount they paid down and then any payments with a new due amount if any or showing paid in ful if this was the final paymentl.
Then my dashboard totals for everything to tack it all on one page
-
Glad to hear things are working. Ninox, like any DB, will have a learning curve. Once you learn Ninox, then you can learn Ninox printing. Something that I've stayed away from. :)
You can rename tables or fields and Ninox will update all refrences to the name in other places.
Now removing fields get a bit trickier. One way to find out what will brake if you delete a field is to make a duplicate DB and delete the field and wait to see what is broken.
Which brings us to the exclamation point you see on tables. That is Ninox telling you you have broken formulas. If you bring up Edit Fields, you can see the fields in red that need attention or deletion.
The sideways M is the math icon for Sum. You have told Ninox to sum the column in that table. You can just remove it.
-
I found this in red.
Not sure what i need to do to fix it?
What is line 1 column 31 how do i see that?
-
Any way to hide a field in place of deleating it?
-
In the template it has Artical so i pick a product but it also has a Invoice Item why do i need pick that that i see no reasion to pick somethig for that? I see it seems to tack all the new invoice items but whay would ot be included on the making a invoice area page.
I put wedding Payment it it i keep picking.
-
The invoice template is assuming that an invoice will have multiple invoice items. Who doesn't want to sell as many items as they can to a customer. :) Thus the need for a table that stores all items for sale (Articles) and then an Invoice Item child table to the Invoice table to track all possible articles being sold to that customer at the time of the invoice.
If you never have more than one item then you can redesign your tables accordingly.
-
Been deleating things and changing names so far it still seems to work took vat out - only one red item left to find and get rid of.
-
Here is a new updated DB sample with all the new changes i made. Also made a Payment / Receipt for printing.
Need to somehow show a new balance due on it now.It is all ready pulling in the current balance due into the invoice so do i need to make a new table or something for it to track each new balance to send into the invoice page to send out?
2022 Biz Database Bata3.ninox
Content aside
- Status Answered
- 1 yr agoLast active
- 19Replies
- 149Views
-
2
Following