Issue getting values for child record from reference record

[RedVox]

I am creating a bot that will add a new invoice record and related invoice_item record.  When creating an invoice & invoice_item without a bot, the user adds an item to the invoice, then, in the invoice_item form, they select a product and values from the product record are copied into the invoice_item.  The bot is intended to enable multiple invoice records to be created as a batch.  The user will select multiple records and use an action item to store a new invoice_id and product_id fields in the member table.  This will trigger the bot.  My problem is in copying the invoice_item values from the product template.

I assumed the initial value formulas in the invoice_item fields would be applied in the bot, but they are not.  So, then I tried using formulas in the bot to populate these fields, but I can't find anything that works.  For example, I need the value of unit_price in the invoice_item record to be the value of unit_price in the product record referenced in the product_id field of the member record:

• [_THISROW].[Product_ID].[Unit_Price]  
• [Product_ID].[Unit_Price]. 
• Lookup([_THISROW].[Product_ID], products, id, unit_price)
• Lookup("Rw222g", products, id, unit_price). // using a hard value of a products[id] field.

None of the above are working as formulas to go in the invoice_item[unit_price] field.Any words of wisdom much appreciated!

[Marc_Dillon]

I assumed the initial value formulas in the invoice_item fields would be applied in the bot, but they are not. 

---

They absolutely should. 

[Product_ID].[Unit_Price]

---

Use that one.

Can you provide some more details about your setup? Screenshots always help.

[RedVox]

Thanks.  I think I've narrowed down my problem to one issue: I cannot find a way to populate my invoice_item[Product] field.  When I create an action to create a new invoice_item, the product field doesn't get populated.  When i hard code a ref value, it works fine, but whenever the value is drawn from another table, in a lookup or select statement,  it does not get populated in the invoice_item[product] field. 

The invoice_items[product] is a ref field for the products table with suggested values of products[id].  I've tried storing the product ref value in a number of tables, I've tried a variety of Select and Lookup formulas and none of it works.  As long as the value is coming from another table and coming via a lookup/select statement, it doesn't seem to work.

 

[Marc_Dillon]

---

@RedVox wrote:

The user will select multiple records and use an action item to store a new invoice_id and product_id fields in the member table.  This will trigger the bot.

---

The "member" table is this "clubs" table that you're showing? How does the contract_template column get populated with a product_id? What exactly triggers the bot?

[RedVox]

I've been trying different things.  I started out with a field in members table called product_template.   I thought the first action would be to use input() to ask the user to select the product_template and I would update the product_template field and one other that would store the UUID for the new invoice record.   But, for some reason, the Input() function keeps getting stuck about 50% of the time.  It raises the dialog but wont let you save the selection.  ( I know it's in beta, but...). So instead, I thought I would store it in the club record so that it's stored once instead of once per member record.  

[Marc_Dillon]

Screenshots and more details please.

[RedVox]

Okay, I think I got it working now.  The trick seems to be that, in my member table, where I was originally trying to save a product ref value, I needed to make that a ref field rather than an enum field with a ref value. I thought I would need to make it an enum so that the dialog input() generates includes radio buttons, but it turns out that if the suggested values are a list, input() generates radio buttons.   Once I turned it into a ref field, the problem went away.  It's weird though because when I look at the MySQL table, nothing has changed.  It's still a text field holding a ref value.  I think enum fields are at the root of all my problems that seem MySQL-specific.  

Two takeaways:  1) input() will use suggested values to determine the dialog it raises. 2) Whenever possible, avoid using enum fields when storing MySQL.  

Thanks for the effort and interest!