I have connected quickbase table to quickbooks to bring in sales receipts.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

However when it brings in the line item I am getting the following formatting in the field instead of just the product name? Any advice on how to make the field only show the product name? Which should be MicroDegree


[('1',1,null,'350.00',null,'SalesItemLineDetail',('MicroDegree','698',null,null,'350',null,null,null,null,'1',null,'NON',null),null), (null,null,null,'350.00',null,'SubTotalLineDetail',null,null)]



Photo of Joshua McAfee

Joshua McAfee

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Are you using QuickBase Sync?
Photo of Joshua McAfee

Joshua McAfee

  • 0 Points
Yes I am using the "Connected Data" and then connecting to quickbooks.
Photo of Jonathan

Jonathan

  • 0 Points
Hi Joshua,

Thanks for bringing this to our attention, we will look into what we can do to improve how this field is pulled in from QuickBooks. However, in the mean time you might want to try creating a formula field with the following formula:

Part([Field],8,"'")

Where [Field] = the name of the field that has the string value you posted in your comment.
This formula should parse out just the name from the string of text you included above.
Photo of Joshua McAfee

Joshua McAfee

  • 0 Points
What kind of forumla field should I convert it to? Text? There are a couple of options. Also where do you place the formula?
Photo of Joshua McAfee

Joshua McAfee

  • 0 Points
Just as a follow up, I tried to convert it to a Forumla Text. But because its connected to quickbooks, its not letting put a formula in there. Like if you where to set up a normal formula text field.
Photo of Jonathan

Jonathan

  • 0 Points
You wouldn't be converting the field, you would be adding a new field to the connected table with a type of "Formula - Text". To do this:
1) Go to your connected table and click the "Settings" link on the top left corner.
2) On the settings page, under Fields, click the "New Fields" drop down and select the "From Scratch" option
3) Name the new field and select the type "Formula - Text". The new field should now be added to your table.
4) Select the field and it will bring you to the "Formula Properties" page
5) In the formula box, replace [field] with the the field that has the value you posted above. The field name has to be in brackets for this to work. So if your field name is Product Name, it needs to be [Product Name] in the formula.
6) Click save and this formula should parse out the name into the new field.
Photo of Joshua McAfee

Joshua McAfee

  • 0 Points
Thanks that worked.

Now where I am stuck is connecting this table (that is connected to quickbooks) back to the contacts. The reason being is I want to display the "customer name" in this "order" table. And I want to link the orders to the contact record.

So like the other tables. I went into contacts and created a relationship from contacts to orders (table) with the reference to the email field, which is the key in the contacts table. The problem is.. it doesn't seem to be syncing. I am not seeing the contact name in the order table (which would reflect the connection), nor do I see the orders in a contacts record so something is going wrong.

I followed your guys directions like this.

-No problem.  Just set the KEY field to your Customer table to be the email field.  In the field list, flag it as the KEY.
-Then make a relationship to each of the Orders Table and the Course table with the "Reference" field in the right side of the Relationship also being the email field.

What might I be doing wrong and is the issue possibly because this is a connected table to quickbooks?
Being connected to another table is unlikely to be the problem.  I think we have sort of jumped threads here.  As I will be traveling for the next day, you may want to post it as a new question where you would describe your relationships.  That way If I cannot get to it someone else might be able to more quickly.
Photo of John

John

  • 0 Points
Hi...I have a bigger problem...Qbooks puts the items sold data in this format when there are many items sold.  Item name, price and qty for many items are all in one record.  Does Qbooks not put this in a real data table..  I really need to break this data into a relational dataset that is linked to a PO number or customer number....

[('1',1,'12 - 4 oz. bags wrapped peppermint puffs - PDQ case','756.00',null,'SalesItemLineDetail',('1240PDQ','195',null,null,'7.56',null,null,null,null,'100',null,'NON',null),null), ('2',2,'12 - 4 oz. wrapped assorted puffs - PDQ case','1512.00',null,'SalesItemLineDetail',('1241PDQ','197',null,null,'7.56',null,null,null,null,'200',null,'NON',null),null), ('3',3,'12 - 4 oz. bags wrapped cinnamon puffs - PDQ case','1058.40',null,'SalesItemLineDetail',('1242PDQ','192',null,null,'7.56',null,null,null,null,'140',null,'NON',null),null), ('4',4,'12 - 4 oz. bags wrapped citrus puffs - PDQ case','756.00',null,'SalesItemLineDetail',('7124PDQ','196',null,null,'7.56',null,null,null,null,'100',null,'NON',null),null), (null,null,null,'4082.40',null,'SubTotalLineDetail',null,null)]