I have a store
I’m created relation with table as inventory and invoice
(inventory::name =invoice_data::name)
how i can get data in bortal row within barcode or name item in portal row acitve field
Hi, @Ahmed , welcome to The Soup!
I strongly encourage you to consider relating products to invoice line items by a unique "ID". Names can change over time and things get fragile. (If the details are to be collected via relationship -- a good approach).
Invoice line items (Invoices--<InvoiceLineItems) can be populated with the product/inventory data by relationship, by lookup, by script, etc.
Since you're asking about a "relationship" one would have a unique product ID on each item in Inventory. In my case I always have a UUID (the primary key) and a serial ID or "lot/item number". It's redundant, but serial IDs are very helpful when debugging systems, identifying network errors, etc.
For the invoice one would have primary key in the invoices table (I use the UUID) and in the invoice line items table one would have a field named something like InvoicesUUID_fk. <= This is the "foreign key" that allows your line times to be displayed in a portal on the invoices form. The Invoices table is the "parent" table and the line items table is the "child" table. Each invoice can have many line items, so this is a "one-to-many" relationship.
Further, the InvoiceLineItems table can have a field named something like "inventLotNum_fk". Then on the relationship graph, a relationship is drawn from Inventory::itemNum to InvoiceLineItems::iventItemNum_fk. This latter field is the one on the invoice in the portal as the item number input field. Be sure to check "allow creation of records via this relationship".
When a user enters the item number into the invoice line item "itemNumber" field (by typing it or scanning a barcode etc.) the details for that product can be pulled from Inventory by relationship.
Alternatively, the above relationship allows for the Invoice Line Item details to be populated in the InvoicesLineItem fields by auto-entered "lookup". This is one of the approaches I typically use. I store all the needed inventory (again) in the InvoicesLineItems table, not just view them by relationship. Inventory details can change so we don't want historical invoices to change when we update the inventory details.
Another option is to not use a relationship, but instead after exit the invoiceLineItems::itemNum field use a script to collect the data and insert it. I use both of the alternative methods mentioned.
This is a pretty verbose reply, but if you'd like clarification on any of it or a sample file. Just ask, many great folds here are equipped to help.
*Edited a couple typos for clarity.
@Ahmed , I remembered that I had created an example file for someone in the community who wanted a simple method of emailing an invoice via the user's local email client. (Sending via SMTP or an API is the preferred method these days, but this is what they requested). It's a VERY simple example, not intended to be beautiful, robust, etc.
I quickly added an inventory table and set it up so one can see the use of the Lookup feature in FileMaker. We could use a script or Auto-enter calculation, etc. I made no attempt to beautify the Inventory layout, nor did I include inventory management tools (update quantity on hand, etc.). I also did not make the invoice read-only after processing (there is no processing), etc. I mention just so those elements may be kept in mind. The point of it was to provide an example of entering a lot number and quantity to populate the portal rows.
Currently there are only four (4) items in inventory. You can see the invoice line items update by entering lot numbers of 101-104. Obviously, you can add more details in Inventory and extend this example. If you have questions about invoicing methods, inventory update, etc. please ask. Details will depend on whether your items are restocked, one of kind (unique item/lot numbers), etc.
InvoiceLineLookup.fmp12 (1.2 MB)
1 Like